CODEWITHSUNDEEP
mysqlmariadbmariasql
pkt
pkt

Reputation: 53

Can't use ALIAS in delete statement in MARIA DB

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

Answers (4)

Reynier
Reynier

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

James
James

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

Nick
Nick

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

ScaisEdge
ScaisEdge

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

Related Questions