Technology . Business . Life

You will need this SQL statement to list all duplicate contents in a table

SELECT email,
COUNT(email) AS NumOccurrences
FROM users
GROUP BY email
HAVING ( COUNT(email) > 1 )

source

Sometimes you may need to populate a table (usually a temporary or new table) from data of another table (usually huge table).

Here’s is the statement that you may use

INSERT INTO “table1″ (”column1″, “column2″, …)

SELECT “column3″, “column4″, …FROM “table2″

source

If you want to update multiple records, usually you will have to do a loop and generate multiple SQL statement like this

UPDATE table_name SET fieldname = value1 WHERE fieldname = field_id1;
UPDATE table_name SET fieldname = value2 WHERE fieldname = field_id2;
UPDATE table_name SET fieldname = value3 WHERE fieldname = field_id3;

But to save some resource (perhaps), you can also update multiple records in just one single MySQL statement

UPDATE table_name
SET fieldname = CASE value_id
WHEN value_id1 THEN ‘value1′
WHEN value_id2 THEN ‘value2′
WHEN value_id3 THEN ‘value3′
END
WHERE value_id IN (value_id1,value_id2,value_id3)