Multiple Values in Update - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Multiple Values in Update

Author

Topic

scottichrosaviakosmos
Yak Posting Veteran

66 Posts

Posted - 2011-01-24 : 13:40:37

i have a 2 tables and i want the data from one table which has multiple data for a data to be updated
to another table;
table 1:
col1 col2
a 1
a 2
b 3
b 4
c 7
c 10

table 2:
col1 col2
1 a
11 b
33 c
44 d
12 e
17 f

i want to update table 2 value by values of table1 on values of a .
as in table 1 a b c has multiple values so i want an update query that can update
table 2 with all the values of a i.e. a =1 and 2 both to be updated in table 2 on a, b ,c .

i wrote a query :
update table2
set col1=a.col2
from table1 where table1.col1=table2.col2

but the above query is only updating 1st value from table 1 but i want all the values of col1 from table 1 to table2.

scoo

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2011-01-24 : 14:34:43

The update is doing exactly what you ask, it is just updating the "duplicate" matches more than once...You are updating 1 ROW with the results of 2 matching records. It updates one match, then updates the record again. You can't ADD records to Table 2 in this fashion. Using a MERGE (aka UPSERT) will also fail

What you need to do is SELECT the matching records into a new table, then replace the data in Table 2 with the results of this new table.

Does the statement return correct results when issued as a SELECT?

for example..
Select
Table2.*, MEWcol1=table1.col2
from table1,table2
where table1.col1=table2.col2

Poor planning on your part does not constitute an emergency on my part.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources