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?