SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


How to update one column if second column matches


How to update one column if second column matches

Author
Message
Shaun2012
Shaun2012
Say Hey Kid
Say Hey Kid (703 reputation)Say Hey Kid (703 reputation)Say Hey Kid (703 reputation)Say Hey Kid (703 reputation)Say Hey Kid (703 reputation)Say Hey Kid (703 reputation)Say Hey Kid (703 reputation)Say Hey Kid (703 reputation)

Group: General Forum Members
Points: 703 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
Andrew Kernodle
Andrew Kernodle
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1336 Visits: 8135
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.

- :-D
Kurt W. Zimmerman
Kurt W. Zimmerman
SSCrazy
SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)

Group: General Forum Members
Points: 2608 Visits: 1400
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
saravanan.a 88885
saravanan.a 88885
SSChasing Mays
SSChasing Mays (609 reputation)SSChasing Mays (609 reputation)SSChasing Mays (609 reputation)SSChasing Mays (609 reputation)SSChasing Mays (609 reputation)SSChasing Mays (609 reputation)SSChasing Mays (609 reputation)SSChasing Mays (609 reputation)

Group: General Forum Members
Points: 609 Visits: 789
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:-).
Shaun2012
Shaun2012
Say Hey Kid
Say Hey Kid (703 reputation)Say Hey Kid (703 reputation)Say Hey Kid (703 reputation)Say Hey Kid (703 reputation)Say Hey Kid (703 reputation)Say Hey Kid (703 reputation)Say Hey Kid (703 reputation)Say Hey Kid (703 reputation)

Group: General Forum Members
Points: 703 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..
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search