Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

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

Posted on 2013-12-17
2
Medium Priority
?
221 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 66

Accepted Solution

by:
Jim Horn earned 2000 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 66

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

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

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

This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

886 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