Hi,

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
1
2
3
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
ID Details
1 a content: 1a
b content: 1b

a content: 1a1
b content: 1b1
2 a content: 2a
b content: 2b
3

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?

THANK YOU!

Ning