Tag Archives: sql statement

Update with join table

Leave a reply

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

Leave a reply

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)

Leave a reply

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)