CODEWITHSUNDEEP
sql-server
loganathan
loganathan

Reputation: 6176

Update Multiple records in single query MS SQL

I am trying to update multiple records in the table using the temporary table using the below query, but doesn't working. Please tell me the proper way to update multiple records.

UPDATE sarufoo 
SET sarufoo.mobile = (SELECT mobile_no FROM logan) 
WHERE sarufoo.homep IN (SELECT homep FROM logan);

Upvotes: 2

Views: 3238

Answers (2)

Jon Egerton
Jon Egerton

Reputation: 41579

One neater way to achieve that be would to join the two tables:

UPDATE sf
SET sf.mobile = l.mobile_no
From
    sarufoo sf
    JOIN logan l ON sf.homep = l.homep

Upvotes: 3

lc.
lc.

Reputation: 116528

You have to correlate the row you are updating with the row you are selecting from. Otherwise your subselect (the SET one) will return every row it has.

UPDATE sarufoo 
SET sarufoo.mobile = (SELECT mobile_no FROM logan WHERE sarufoo.homep = logan.homep) 
WHERE sarufoo.homep IN (SELECT homep FROM logan);

Upvotes: 0

Related Questions