sql snippets for mysql querying

snippets.sql

-- select limited rows (paging) and left join additional data, http://stackoverflow.com/a/16767018/289404
SELECT t1.*, t2.*
FROM (SELECT * FROM table t WHERE t.id > 100 LIMIT 100) t1
LEFT JOIN table2 t2 ON t1.id = t2.id
ORDER BY t1.id DESC

-- select table column names
SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE table_name='table';

-- find duplicate
SELECT * FROM product_price GROUP BY product_id HAVING COUNT(*) >= 2;

-- replace string in rows
UPDATE mod_wysiwyg SET content = REPLACE( content, '/dev', '' );

-- select a modified record set
SELECT REPLACE( column, '/dev', '' ) FROM table;

-- select by specified order
SELECT column FROM table WHERE parent_id = 0 ORDER BY find_in_set(id, '4,5,3,2,1');

-- row cloning
INSERT INTO table (column1, column2,column3, column4)
SELECT column1, 234, column4, NOW()
FROM table
WHERE id IN (6,7)

-- insert 100k rows for testing, http://stackoverflow.com/a/3766382/289404
DELIMITER $$
CREATE PROCEDURE insert_test_data()
BEGIN
  DECLARE i INT DEFAULT 1;

  WHILE i < 100000 DO
    INSERT INTO `mod_firm` (`user_id`, `page_id`, `idnumber`, `vatnumber`, `eu_vatnumber`, `description`, `active`, `created`, `expire`)
    SELECT `user_id`, `page_id`, `idnumber`, `vatnumber`, `eu_vatnumber`, `description`, `active`, `created`, `expire`
    FROM `mod_firm`
    WHERE id = 1;
    SET i = i + 1;
  END WHILE;
END$$
DELIMITER ;
CALL insert_test_data();
DROP PROCEDURE insert_test_data;