CODEWITHSUNDEEP
mysqlsql-update
Thomas Fischer
Thomas Fischer

Reputation: 21

MySQL UPDATE QUERY Duplication error

Let's see if I can nail down the question because it's pretty complex to my mid-level MySQL skills.

PROBLEM:

I have a need to update some data in one of my DB tables. The problem is the data I am updating is tied to more than one row.

id         mailing_list_id         subscriber_id

9031                 51                 81053

9112                 52                 81053

13535               71                 81053

I need to update mailing_list_id 52 and rename it 51 but the subscriber_id already exists with mailing_list_id of 51 so I want to skip that record during my update.

When I use the following:

UPDATE subscriptions SET mailing_list_id = 51 WHERE mailing_list_id = 52;

I get an error saying there is a duplicate entry.

What I need to do is ignore duplicates in my query but I don't know how to write the query. Any help would be appreciated.

I need to add something to my query to skip the rows where the subscriber id already has a mailing list id # the same as the one I am trying to change it to. Not sure if that makes sense or if at this point I am rambling. I know what I want to do I just don't know the query syntax or method to make it happen.

Upvotes: 0

Views: 353

Answers (2)

user1068963
user1068963

Reputation:

What field mailing_list_id have a index (unique or primary)? if yes so you cannot change which the data exists in other row.

The solution is: 1. You remove extra key(unique or primary) in field mailing_list_id. 2. Or you create new table and remove your table, after you remove table subscription, run query in below to create new table subscription.

CREATE TABLE subscriptions (
     id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
     mailing_list_id INT(11),
     subscriber_id INT(11)
);

Upvotes: 2

SeanCannon
SeanCannon

Reputation: 78046

While I don't recommend this as a permanent solution, this should get rid of your errors so you can get things working while you plan out a better schema - unique keys, REPLACE INTO, etc. 

UPDATE IGNORE subscriptions SET mailing_list_id = 51 WHERE mailing_list_id = 52;

Upvotes: 0

Related Questions