how to update few records thru join

[irinamas]

I have two tables: table1 with columns (ID, A1, A2) and table2 with columns (ID, newA1, newA2). I need to update values in table 1 with new values from table 2 only where IDs match. My query doesn't work even for one column:

update table1b
set b.A1= (select a.newA1 from table 2 a
where a.ID=b.ID)

Inner query returns multiple records, and the whole thing doesn't work.

Any suggestions? Thanks.

[Pilgrim333]

I have two tables: table1 with columns (ID, A1, A2) and table2 with columns (ID, newA1, newA2). I need to update values in table 1 with new values from table 2 only where IDs match. My query doesn't work even for one column:

update table1b
set b.A1= (select a.newA1 from table 2 a
where a.ID=b.ID)

Inner query returns multiple records, and the whole thing doesn't work.

Any suggestions? Thanks.

Hi,

In the subquery you have the restrict the returned values. If all the values of a.newA1 are all the same, you can use a distinct in the subquery. If they are not all the same, then you have to restrict the subquery some more, so that it returns just one value for each id.

Pilgrim.