Tag Archives: sql statement

Update with join table

To update a table with a condition within other table (to join table), you can use this statement

1
2
3
UPDATE table1 a, table2 b
SET a.field6 = value
WHERE a.field1 = b.field1 AND b.field2 = value2

SQL statement to list duplicate contents

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

If you want to list all duplicate records

SELECT t1.* FROM
`some_table` t1,
( SELECT documentno, COUNT(1) AS dup
  FROM `some_table`
  GROUP BY documentno
  HAVING dup > 1 ) AS t2
WHERE t1.documentno = t2.documentno;

Then how to remove the duplicate records?

1. Create new table and insert clean records (without duplicate)

CREATE TABLE new_table AS
SELECT * FROM old_table WHERE 1 GROUP BY [COLUMN TO remove duplicates BY];

Note: Be careful, this will create a table without primary key, auto-increment etc. So make sure you update your table after run this statement

2. Drop old table (with duplicates)

source

Updating multiple records in one sql statement (MySQL)

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)