CODEWITHSUNDEEP
mysql
Omari Victor Omosa
Omari Victor Omosa

Reputation: 2879

SQL Error [1064] [42000]: You have an error in your SQL syntax when using ALTER IGNORE TABLE on mysql 8

This one works in older mysql versions, but the good thing with this query is that it will remove duplicates and same time add index, how do i do the same in mysql 8 commercial

ALTER IGNORE TABLE tablex
ADD UNIQUE INDEX id_rmd (field1,field12, field3);

SQL Error [1064] [42000]: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'IGNORE TABLE ...

How do i do the same in mysql 8 commercial. i.e. remove duplicate while same time create index

Upvotes: 0

Views: 705

Answers (1)

ysth
ysth

Reputation: 98508

Commercial or not doesn't make a difference. INSERT IGNORE was deprecated in 5.6 and removed in 5.7:

https://dev.mysql.com/doc/refman/5.6/en/alter-table.html:

IGNORE is a MySQL extension to standard SQL. It controls how ALTER TABLE works if there are duplicates on unique keys in the new table or if warnings occur when strict mode is enabled. If IGNORE is not specified, the copy is aborted and rolled back if duplicate-key errors occur. If IGNORE is specified, only one row is used of rows with duplicates on a unique key. The other conflicting rows are deleted. Incorrect values are truncated to the closest matching acceptable value.

As of MySQL 5.6.17, the IGNORE clause is deprecated and its use generates a warning. IGNORE is removed in MySQL 5.7.

You can just rename and copy the table (untested):

create tablex_new like tablex;
ALTER TABLE tablex_new
ADD UNIQUE INDEX id_rmd (field1,field12, field3);
insert ignore into tablex_new select * from tablex;
rename table tablex to tablex_old, tablex_new to tablex;
-- after verifying success
drop table tablex_old;

(Note that any inserts/updates/deletes that happen between the insert ignore starting and the rename completing will be lost.)

Upvotes: 1

Related Questions