#mysql MySQL query snippets

readme.md

Insert with select

INSERT INTO table1 ( column1 )
SELECT  col1
FROM    table2  

Show key value row based (e.g., meta) table as columns with main table

Example: if you have users(id, email) and user_meta(user_id, key, value) tables where you have phone, age etc keys in user_meta but need to show these as columns with users table

SELECT 
u.id, u.email, 
GROUP_CONCAT(IF(um.meta_key='phone', um.meta_value, NULL)) AS phone, /* NULL so that other columns do not print anything */
GROUP_CONCAT(IF(um.meta_key='verified', IF(um.meta_value=1, 'Yes', 'No'), NULL)) AS verified
FROM users u
JOIN user_metas um ON u.id=um.user_id
GROUP BY u.id

Delete from a table when it is referenced in join or subquery

DELETE FROM table
WHERE id IN (
 SELECT id
 FROM table
 WHERE arg = 1 AND foo = 'bar'
);

will give you the following error

You can't specify target table 'table' for update in FROM clause

however the following will work

DELETE FROM table
WHERE id IN (
  SELECT id FROM (
    SELECT id
    FROM table
    WHERE arg = 1 AND foo = 'bar'
  ) x
);