This is a little tip that a co-worker shared with me some time ago and has made my life so much easier. I am often faced with a situation very similar to this: let’s say we have a table called users_rooms that includes the columns user, room, deleted, created and modified. I am required to insert new rows in this table where the values of the room column for user 123 should match all the available ones for the same column for user 128. This is something easy to do with subqueries, like this:
"insert into users_rooms (user, room, created, modified) (select 123, room, NOW(), NOW() from users_rooms where user = 128) on duplicate key update deleted = 0, modified = NOW()";
This query will effectively insert a series of rows where the user will be 123, created and modified will be the current time and the values for the room column will match those of user 128 . Easy!