Help with updating column in one table with values from table in another table.
I have two tables (TableA and TableB). I would like to update the values in TableA NEWCOLUMN to match the values in TableB OLDCOLUMN when ID is equal. NEWCOLUMN and OLDCOLUMN are geometry columns.
TableA
ID | NEWCOLUMN
0 |
1 |
2 |
3 |
4 |
5 |
TableB
ID | OLDCOLUMN
------------------
1 | a
2 | b
3 | c
4 | d
5 | e
6 | f
7 | g
8 | h
I had used the following query in the past, but am now getting an error when I try to update.
I get the following error:
TableA
ID | NEWCOLUMN
0 |
1 |
2 |
3 |
4 |
5 |
TableB
ID | OLDCOLUMN
------------------
1 | a
2 | b
3 | c
4 | d
5 | e
6 | f
7 | g
8 | h
I had used the following query in the past, but am now getting an error when I try to update.
UPDATE TableA
SET TableA.NEWCOLUMN = (SELECT TableB.OLDCOLUMN FROM TableB WHERE TableA.ID = TableB.ID)I get the following error:
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
btw The reason for the error is whenever you have a 'SET column_name = (subquery)', when we're assigning the return set of a query to a scalar variable, the (subquery) can only return one value.
>SELECT TableB.OLDCOLUMN FROM TableB WHERE TableA.ID = TableB.ID
Which returned every ID where there was a match, hence the error.