Gabi Jack's Blog
  • About

Tag Archives: subqueries

Quick Tip: MySQL Subqueries

Posted on December 30, 2013 by Posted in MySQL, Quick Tip

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!

MySQL SQL subqueries
February 2019
S M T W T F S
« Dec    
 12
3456789
10111213141516
17181920212223
2425262728  

Recent Posts

  • TIL: Working with a D3-based chart library
  • Diary of a Junior Dev: The Joys of Building
  • In search of a faster query
  • Living la vida Ruby
  • I love peer reviews… I hate peer reviews

Recent Comments

    Categories

    CyberChimps

    CyberChimps

    Marketed By Neil Patel
    © Gabi Jack's Blog