• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 224
  • Last Modified:

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.

UPDATE TableA
SET TableA.NEWCOLUMN = (SELECT TableB.OLDCOLUMN FROM TableB WHERE TableA.ID = TableB.ID)

Open in new window



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.
0
VAMS1
Asked:
VAMS1
  • 2
1 Solution
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Give this a whirl..
UPDATE TableA
SET TableA.NEWCOLUMN = TableB.OLDCOLUMN
FROM TableA
   JOIN TableB ON TableA.ID = TableB.ID

Open in new window

btw Nice job laying out your question.
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
>Subquery returned more than 1 value.
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.
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now