This is sample query to delete record from multiple tables.
Specify all joined tables to get the list but can specify records in which tables to be deleted.
DELETE t1, t2 FROM t1 INNER JOIN t2 INNER JOIN t3 WHERE t1.id=t2.id AND t2.id=t3.id;
OR
DELETE FROM t1, t2 USING t1 INNER JOIN t2 INNER JOIN t3 WHERE t1.id=t2.id AND t2.id=t3.id;
Reference
https://dev.mysql.com/doc/refman/8.0/en/delete.html (refer section – Multi-Table Deletes)
https://dev.mysql.com/doc/refman/5.0/en/comparison-operators.html#function_coalesce
COALESCE()
IN
NOT_IN
NOT_LIKE
FIND_IN_SET
Example: select * from users where (find_in_set (5, access_string) > 0);
will return position of the key if found in string. else will return 0 for not found or NULL if field is NULL
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 |
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.
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 ) |
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)
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)