CODEWITHSUNDEEP
sqlsql-serversql-server-2008
Passionate Engineer
Passionate Engineer

Reputation: 10412

SQL update rows from another set of rows

I've got below SQL query:

UPDATE t1
SET t1.selected_team_id = t2.selected_team_id, t1.source_id = t2.source_id
FROM Tip t1, (SELECT * FROM Tip WHERE tip_id = 19494067) t2 -- To
WHERE t1.tip_id = 27471255 -- From

This allows me to copy tip_id 19494067 and update selected_team_id and source_id on tip_id 27471255

Now I want to be able to copy a number of rows lets say 6 rows from 19494067 - 19494073 to 27471255 - 27471261

I tried WHERE IN but this just seems to copy the first row only (19494067) for all of the rows in 27471255 - 27471261

Is there a good way to do this in batch with a single call?

Upvotes: 1

Views: 49

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270573

If the rows are adjacent, you can do this. Something like:

UPDATE tot
    SET selected_team_id = fromt.selected_team_id,
        source_id = fromt.source_id
FROM Tip tot join
     Tip fromt
     on fromt.tip_id - 19494067 = tot.tip_id - 27471255
WHERE tot.tip_id between 27471255 and 27471261;

Upvotes: 1

Related Questions