Help with Update query

Question

Post reply

Help with Update query

sap55

Old Hand

Points: 322
More actions
July 19, 2010 at 3:37 am

#223499

Hi, I need to update a column in a table by comparing its date column with another tables date range.
Pls advise.
table1-----------table2
col1(PK,id)------col1(FK,id)------given
col2(FK,id)------col2(PK,id)
col3(date field)
-----------------col4(start date field)
-----------------col5(end date field)
Joining on col1, and comparing col3 date field to be between col4 and col5, i need to update table1 col2(fk,id) field with table2's col2(pk,id). Will the following work?
update table1
set table1.col2=table2.col2
from table2
join table1
on table1.col1 = table2.col1
and table1.col3>=table2.col4
and table1.col3<=table2.col5
Thanks!

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

sql-programmers SSC-Addicted Points: 494 More actions · Answer 1

Yes your script will work for your situation.

SQL Sever Performance tuning

sk-458789 SSC Enthusiast Points: 188 More actions · Answer 2

Check out this....

update table1

set col2=B.col2

from table1 A, table2 B

Where A.col1 = B.col1 and A.col3>=B.col4 and A.col3<=B.col5

SKC

sap55 Old Hand Points: 322 More actions · Answer 3

Thanks everyone....Joe, thanks for the insight.

sap55 Old Hand Points: 322 More actions · Answer 4

Thanks for the reply CELKO!. How do you troubleshoot the following error message

Msg 8672, Level 16, State 1, Line 1

The MERGE statement attempted to UPDATE or DELETE the same row more than once. This happens when a target row matches more than one source row. A MERGE statement cannot UPDATE/DELETE the same row of the target table multiple times. Refine the ON clause to ensure a target row matches at most one source row, or use the GROUP BY clause to group the source rows.

Is there a similar functionality available with SSIS? Thanks.