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: Wednesday, August 20, 2014 9:45 AM
Points: 134, Visits: 419
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
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Yesterday @ 3:12 PM
Points: 607, Visits: 6,659
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: Thursday, August 21, 2014 9:40 AM
Points: 985, Visits: 1,326
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
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Yesterday @ 6:15 AM
Points: 237, Visits: 376
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: Wednesday, August 20, 2014 9:45 AM
Points: 134, Visits: 419
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