Can I use SQL to update multiple rows with multiple values from another table?
- by stu
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?