Jump to content

Delete dupe rows using column alias


niche
 Share

Recommended Posts

this script deletes dupe rows when table_key is a column:

DELETE t1 FROM obssum_history  t1
INNER JOIN obssum_history  t2 
WHERE 
    t1.id < t2.id AND 
    t1.table_key = t2.table_key

 how would I do that when table_key is a column alias?

Already tried something like this (when table_key is NOT contained in the table):

USING CONCAT(col1, col2) AS table_key
DELETE t1 FROM obssum_history  t1
INNER JOIN obssum_history  t2 
WHERE 
    t1.id < t2.id AND 
    t1.table_key = t2.table_key

didn't work.

Edited by niche
Link to comment
Share on other sites

Turns out this is the answer:

WHERE t1.id < t2.id 
  AND CONCAT(t1.col1,t1.col2) = CONCAT(t2.col1,t2.col2)

Duh!

Edited by niche
Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

×
×
  • Create New...