Archive

Archive for the ‘MySQL’ Category

Delete from same table as select

August 23, 2012 Leave a comment

Simple code to delete from same table as when selecting from it. MySQL doesn’t allow you to delete from same table as you select.

So this is not possible:

DELETE FROM chess inner join tennis on chess.some_id = tennis.some_id where chess.some_col = ‘great’;

You will get an error saying: You can’t specify target table ‘chess’ for update in FROM clause.

So you need to create a temp table and then delete.

DELETE FROM chess where some_id in (select some_id from
(SELECT chess.some_id FROM chess inner join tennis on tennis.some_id = chess.some_id where some_col = ‘great’
) as y);

Done, easy.

Advertisements
Categories: Coding, MySQL

Cleaning tables of duplicates

August 15, 2012 Leave a comment

Cleaning a MySQL database called presschoice that has combined duplicate values candidate_id and press_id columns. The table only has 3 columns, presschoice_id (primary key, auto_increment), candidate_id (int), presschoice_id (int).

# first checking how many duplicates you have

SELECT * FROM presschoice p;
select
candidate_id,
press_id,
count(*) as n

from presschoice
group by candidate_id,press_id
having n > 1

# creating a temp table to store the distinct values
create temporary table presschoice_clean2 as SELECT distinct candidate_id,press_id FROM presschoice p;

# deleting the orginal values from the table. Make sure you have a backup!
delete from presschoice;

# add the new “clean” values.
insert into presschoice(candidate_id, press_id) select candidate_id,press_id from presschoice_clean2;

# also deleting any rogue data
delete from presschoice where candidate_id=0;

Looking into creating a Trigger so the duplicate values don’t go in. That maybe in next post!

Categories: Coding, MySQL Tags: