Tag Archives: sql

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

MySQL insert into select

This SQL statement to be used to insert data into a table from another different table.

1
2
3
INSERT INTO table1 (field1)
  SELECT table2.field2 FROM table2 
  WHERE table2.field2 > 100;

Be careful with ambiguous fields. So put the table name in front of the field name.
Also be careful with duplicate data inserted to the table.

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

MySQL search and replace

I’ve been doing data cleansing of one of my project. Then there was a need to find some abbreviation to be replaced by the real words.

Here I did with mysql statement.

update property set town = replace(town,’tmn’,’taman’)

In general:

update TABLENAME set FIELDNAME = replace (FIELDNAME, searchstr, replacestr)