Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

How to update one column if second column matches Expand / Collapse
Author
Message
Posted Thursday, October 10, 2013 2:21 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, December 16, 2014 2:19 PM
Points: 135, Visits: 423
Hi,
I have 2 tables, from which i have to update as below

table Source: col a, col b

table destination: col d, col e

if source.b = destination.e then
update destination
set d = a

Any clue how to do...


Shaun
Post #1503800
Posted Thursday, October 10, 2013 2:24 PM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Tuesday, December 23, 2014 3:08 PM
Points: 598, Visits: 7,224
Barring any missing criteria, this should be an UPDATE...FROM query:

UPDATE Destination
SET Destination.d = Source.a
FROM Destination
INNER JOIN Source
ON Destination.e = Source.b

Test this first, of course, before firing it off as an actual UPDATE.




-
Post #1503801
Posted Thursday, October 10, 2013 2:30 PM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Tuesday, December 9, 2014 11:09 AM
Points: 989, Visits: 1,329
I had it somewhat different, maybe too complex:

;with which_col_a_cte (col_a, col_d, col_e) on (
select s.col_a, d.col_d, d.col_e
from destination d inner join
source s on d.e = s.b
)
update destination
set col.d = cte.col.a
from destination d inner join
which_col_a_cte cte on d.col_e = cte.col_e
and d.col_d = cte.col_d


Without any real data I wasn't able to test it, but I think it looks good.


Kurt W. Zimmerman
SR DBA
Lefrak Organization
New York, NY

http://www.linkedin.com/in/kurtwzimmerman
Post #1503802
Posted Friday, October 11, 2013 12:57 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Monday, December 22, 2014 1:30 AM
Points: 350, Visits: 488
Hi Kurt it is complex but nice one,i thing you used CTE for update process i am stater of CTE as of now i have seen only select queries but i hope it will help me on that type of scenarios Thanks.
Post #1503890
Posted Friday, October 11, 2013 7:16 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, December 16, 2014 2:19 PM
Points: 135, Visits: 423
hi kurt,

I did had the query but was the performance issues..i was looking to use ssis components such as lookup..

Shaun..
Post #1503997
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse