Solved

Help with updating column in one table with values from table in another table.

Posted on 2013-12-17
2
213 Views
Last Modified: 2013-12-18
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
Comment
Question by:VAMS1
  • 2
2 Comments
 
LVL 65

Accepted Solution

by:
Jim Horn earned 500 total points
ID: 39724148
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
 
LVL 65

Expert Comment

by:Jim Horn
ID: 39724167
>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

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

839 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question