I’m using this query to remove duplicates from phoenix_tasks table:
CREATE TEMPORARY TABLE temp
SELECT `piva`
FROM phoenix_tasks
GROUP BY `piva`
HAVING COUNT(*) > 1;
DELETE FROM phoenix_tasks WHERE `piva` IN (SELECT `piva` FROM temp) AND taskStateId=1 ;
DROP TEMPORARY TABLE temp;
It works correctly in phpMyAdmin
But when the query is executed by web I receive this error message:
CREATE TEMPORARY TABLE temp
SELECT piva
FROM phoenix_tasks
GROUP BY piva
HAVING COUNT (*) > 1;
DELETE
FROM phoenix_tasks
WHERE piva IN (
SELECT piva
FROM temp
)
AND taskStateId=1: You have an error in your SQL syntax;
check the manual that corresponds to your MariaDB server version for the right syntax to use near '
DELETE
FROM phoenix_tasks
WHERE piva IN (
SELECT piva
FROM temp
)
AND taskStat' at line 7
I found at https://mariadb.com/kb/en/useful-mariadb-queries/ this:
Removing Duplicates
CREATE TABLE t (id INT, f1 VARCHAR(2));
INSERT INTO t VALUES (1,'a'), (2,'a'), (3,'b'), (4,'a');
SELECT * FROM t t1, t t2 WHERE t1.f1=t2.f1 AND t1.id<>t2.id AND t1.id=(
SELECT MAX(id) FROM t tab WHERE tab.f1=t1.f1
);
DELETE FROM t WHERE id IN (
SELECT t2.id FROM t t1, t t2 WHERE t1.f1=t2.f1 AND t1.id<>t2.id AND t1.id=(
SELECT MAX(id) FROM t tab WHERE tab.f1=t1.f1
)
);
It seems to be exactly the query I’m trying to run (except for the presence in my query of temporary and the different construction of the temp table)
I’m not able why it has errors.
2
Answers
It seems that your "web execution tool" does not like multiple SQL statements to be run in a single call.
I would try running all three, but one at a time.
(The stated Question is already Answered — run the statements separately. I’m addressing the task that led to the Question.)
Another way to de-dup a table:
But, as already mentioned, run the queries one at a time.
Note: Before the
DROP
, you have a chance to verify that the process worked correctly.Notes:
UNIQUE(piva)
. You may be able to control which dup wins by adding anORDER BY
onto theSELECT
.PRIMARY KEY
? Would it make sense to replace it withPRIMARY KEY (piva)
?INSERT
is slow; theRENAME
is fast and atomic.