select multiple rows from t2 and copy the content to t1.details
Any help with this question is greatly appreciated!
Suppose you have two tables X and Y:
table X: ID Details ---ID is unique, details is null
table Y: ID A B ---id is NOT unique
1 1a 1b
1 1a1 1b1
2 2a 2b
Now I want to update X.details to contain the content of Y.a and Y.b for
all rows in Y such that Y.id==X.id.
The resulting table X should be
1 a content: 1a
b content: 1b
a content: 1a1
b content: 1b1
2 a content: 2a
b content: 2b
And here's the SQL I wrote:
UPDATE X t1 SET DETAILS=
(SELECT 'a content: ' || a || CHR(10) ||
'b content: ' || b || CHR(10) || CHR(10)
FROM Y t2
WHERE t2.id=t1.id AND t2.a IS NOT NULL)
But it gives me an error: "single-row subquery returns more than one row",
since Y.id is not unique.
The question is: How do I loop through all rows of Y and copy the content
of the appropriate rows to X.details to get the resulting table?
Top DevX Stories
Easy Web Services with SQL Server 2005 HTTP Endpoints
JavaOne 2005: Java Platform Roadmap Focuses on Ease of Development, Sun Focuses on the "Free" in F.O.S.S.
Wed Yourself to UML with the Power of Associations
Microsoft to Add AJAX Capabilities to ASP.NET
IBM's Cloudscape Versus MySQL