Click here to monitor SSC
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
SSC-Enthusiastic
SSC-Enthusiastic (135 reputation)SSC-Enthusiastic (135 reputation)SSC-Enthusiastic (135 reputation)SSC-Enthusiastic (135 reputation)SSC-Enthusiastic (135 reputation)SSC-Enthusiastic (135 reputation)SSC-Enthusiastic (135 reputation)SSC-Enthusiastic (135 reputation)

Group: General Forum Members
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
Andrew Kernodle
Andrew Kernodle
SSChasing Mays
SSChasing Mays (610 reputation)SSChasing Mays (610 reputation)SSChasing Mays (610 reputation)SSChasing Mays (610 reputation)SSChasing Mays (610 reputation)SSChasing Mays (610 reputation)SSChasing Mays (610 reputation)SSChasing Mays (610 reputation)

Group: General Forum Members
Points: 610 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
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1062 Visits: 1396
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
SSC-Addicted
SSC-Addicted (482 reputation)SSC-Addicted (482 reputation)SSC-Addicted (482 reputation)SSC-Addicted (482 reputation)SSC-Addicted (482 reputation)SSC-Addicted (482 reputation)SSC-Addicted (482 reputation)SSC-Addicted (482 reputation)

Group: General Forum Members
Points: 482 Visits: 755
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
SSC-Enthusiastic
SSC-Enthusiastic (135 reputation)SSC-Enthusiastic (135 reputation)SSC-Enthusiastic (135 reputation)SSC-Enthusiastic (135 reputation)SSC-Enthusiastic (135 reputation)SSC-Enthusiastic (135 reputation)SSC-Enthusiastic (135 reputation)SSC-Enthusiastic (135 reputation)

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