Jump to content

Must...remove...duplicates


ThePsion5
 Share

Recommended Posts

Hi, I'm trying to insert a large amount of data (800-1000 entries) into an SQL table from the command prompt from another table using the following query:

INSERT INTO ClientsToKeywords SELECT DISTINCT * FROM Derived2;

Both ClientsToKeywords and Derived2 have the same fields and types, that's not the issue. But some of the Derived2 entries violate a unique constraint applied to the ClientsToKeywords table, so whenever the MySQL server hits a tuple that violates the unique constraint it simply stops the insertion. While i could search my entered data for any rows that violate the constraint, this would take more time than I care to invest. Is there a way to have MySQL do it for me either in the select statement or another SQL query on the Derived2 table itself? Thanks in advance!

Link to comment
Share on other sites

Ok, I looked around the web and found a command I thought might work:

INSERT INTO ClientsToKeywords '0', (SELECT DISTINCT ClientID, KeywordID FROM Derived2), '1';

This gave my a syntax error, but the following slightly modified worked successfully:

INSERT INTO ClientsToKeywords (SELECT DISTINCT '0', ClientID, KeywordID, '1' FROM Derived2);

(the 0 and 1 values are the auto-incrementing ID and another non-constrained field). Hope this helps anyone else who stumbles upon this thread, lol.

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...