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, March 25, 2014 2:02 PM
Points: 109, Visits: 363
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
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Yesterday @ 2:53 PM
Points: 489, Visits: 5,464
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.


----------------------------------
My journal of things I'm learning about SQL
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: Yesterday @ 1:13 PM
Points: 958, Visits: 1,285
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-Enthusiastic

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

Group: General Forum Members
Last Login: Today @ 12:40 AM
Points: 195, Visits: 293
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, March 25, 2014 2:02 PM
Points: 109, Visits: 363
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