开发者

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

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.

开发者_如何学C

Is this even possible in one step?


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)


MERGE was introduced in SQL-99 Standard, refined in SQL:2003 and implemented (and extended) by several vendors (e.g. SQL Server 2008, Oracle, postgreSQL):

MERGE INTO YourTable
   USING (
          SELECT userid, trait, onoff
            FROM YourTable
           WHERE userid = 2
         ) AS S
      ON YourTable.userid = 1
         AND S.trait = YourTable.trait
WHEN MATCHED THEN 
   UPDATE
      SET onoff = S.onoff
WHEN NOT MATCHED THEN
   INSERT (userid, trait, onoff)
      VALUES (userid, trait, onoff);
0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜