CODEWITHSUNDEEP
mysqlsqldatabase
zenCoder
zenCoder

Reputation: 740

Remove duplicates condition to column value

I have two tables - Table_1 and Table_2.

They have identical columns - Col1, Col2, Col3, Col4(integer).

Col4 value for all Table_1 rows is 1.
Col4 value for all Table_2 rows is 2.

I insert Table_2 rows into Table_1.

Table_1 and Table_2 have some duplicate values based on Col1 and Col2. I need to retain the Table_2 values and delete the Table_1 duplicate values.

For example:

   Col1,Col2,Col3,Col4
1) a   ,b   ,c   ,1      (From Table_1)
2) a   ,b   ,d   ,2      (From Table_2)

I tried the following -

ALTER TABLE Table_1 ADD UNIQUE (Col1, Col2);

It removed the Table_2 values instead of Table_1 values.

How do I solve this problem?

Upvotes: 0

Views: 150

Answers (2)

Jeff
Jeff

Reputation: 918

Another alternative. Create the unique key on col1, col2 and use the REPLACE INTO syntax.

REPLACE INTO Table_1 (col1, col2, col3, col4)
SELECT col1, col2, col3, col4 FROM Table_2

If a duplicate record exists, it will delete the record in Table_1 and insert the record from Table_2. If the record doesn't exist, it simply inserts it.

Upvotes: 0

ethrbunny
ethrbunny

Reputation: 10469

Put a unique key on col1, col2 and use on duplicate key update col3=VALUES(col3), col4=VALUES(col4) when you insert.

Upvotes: 1

Related Questions