Reputation: 53
I am trying to delete dupes from a table but it's not letting me use alias. Tried various solutions given in several forums.
the query is,
DELETE FROM `table` AS t1
WHERE EXISTS (
SELECT 1 FROM `table` AS t2
WHERE t2.`col1` = t1.`col1`
AND t2.`col2` = t1.`col2`
AND t2.id > t1.id )
Upvotes: 0
Views: 492
Reputation: 949
You could try this, is more complex but more secure by far because using inner inside delete could be really dangerous, in this way you can check what you want to delete first:
SET @discriminator = 0;
SET @p1 = null;
SET @p2 = null;
CREATE TEMPORARY TABLE temp_rows(
`id` INT PRIMARY KEY,
`index` INT,
`col1` COL1TYPE,
`col2` COL2TYPE
);
INSERT INTO temp_rows (`index`, `col1`, `col2`, `id`)
SELECT CASE WHEN @p1 != col1 OR @p2 != col2 THEN @discriminator := 0 ELSE @discriminator := @discriminator + 1 END AS 'index',
@p1 := col1 AS 'col1',
@p2 := col2 AS 'col2',
id
FROM `schema`.table
ORDER BY col1, col2, id desc;
DELETE FROM table WHERE EXISTS (
SELECT 1 FROM `temp_rows`
WHERE table.`id` = temp_rows.`id`
);
DROP TEMPORARY TABLE temp_rows;
Upvotes: 1
Reputation: 1829
You can use left join
to delete duplicate in the same table when id
is primary key
delete t1
from `table` as t1
left join `table` as t2 on t1.col1 = t2.col1 and t1.col2 = t2.col2 and t1.id < t2.id
where t2.id is not null;
Upvotes: 0
Reputation: 147286
You can use a multi-table DELETE
statement:
DELETE t1
FROM `table` t1
JOIN `table` t2 ON t2.col1 = t1.col1 AND t2.col2 = t1.col2 AND t2.id > t1.id
Upvotes: 2
Reputation: 133410
You could try using an inner join instead of exist subquery
DELETE t
FROM `billing_squarecustomer` t
INNER JOIN (
SELECT t2.`patient_id`.
FROM `billing_squarecustomer` AS t2
INNER JOIN `billing_squarecustomer` AS t1
WHERE t2.`patient_id` = t1.`patient_id`
AND t2.`merchant_id` = t1.`merchant_id`
AND t2.id > t1.id
) tdel = tdel.patient_id = t.patient_id
Upvotes: 1