Can I use SQL to update multiple rows with multiple values from another table?

Question

I've got a one to many table with ids and traits for those users. I want to copy one user's settings to another.

userid    trait    onoff
------    -----    -----
1          1         on
1          2         on
1          3         on
1          4         on
2          1         off
2          2         off
2          3         off
2          4         off

So I want to copy the onoff values from user 1 to 2, so that user2 has everything on when I'm done.

update table set onoff = (select onoff from table where userid=1) where userid=2

that's the basic idea, but obviously doesn't work. I imagine I want the server to think in terms of a correlated subquery, but the outer part isn't a query, it's an update.

Is this even possible in one step?

Answer 1

try this, userid=2 will get useid=1 values:

UPDATE u2
    SET onoff=u1.onoff
    FROM YourTable  u2
        INNER JOIN YourTable u1 ON u2.trait=u1.trait    
    WHERE u2.userid=2 AND u1.userid=1

full SQL Server 2005+ example:

DECLARE @YourTable table (userid int,trait int, onoff varchar(3))
INSERT INTO @YourTable VALUES (1,          1,         'on')
INSERT INTO @YourTable VALUES (1,          2,         'on')
INSERT INTO @YourTable VALUES (1,          3,         'on')
INSERT INTO @YourTable VALUES (1,          4,         'on')
INSERT INTO @YourTable VALUES (2,          1,         'off')
INSERT INTO @YourTable VALUES (2,          2,         'off')
INSERT INTO @YourTable VALUES (2,          3,         'off')
INSERT INTO @YourTable VALUES (2,          4,         'off')

select * from @YourTable order by userid,trait

UPDATE u2
    SET onoff=u1.onoff
    FROM @YourTable  u2
        INNER JOIN @YourTable u1 ON u2.trait=u1.trait    
    WHERE u2.userid=2 AND u1.userid=1

select * from @YourTable order by userid,trait

output

userid      trait       onoff
----------- ----------- -----
1           1           on
1           2           on
1           3           on
1           4           on
2           1           off
2           2           off
2           3           off
2           4           off

(8 row(s) affected)

(4 row(s) affected)

userid      trait       onoff
----------- ----------- -----
1           1           on
1           2           on
1           3           on
1           4           on
2           1           on
2           2           on
2           3           on
2           4           on

(8 row(s) affected)