CODEWITHSUNDEEP
mysql
Rivs
Rivs

Reputation: 89

Deleting conflicting record for composite key before update statement

I have a table which has composite key on type, entityId, userid. Now i want to alter table record for some reason. Issue with updating record that update statement gives error like duplicate key.

For example my query is like —

UPDATE iap SET type = 'tour', entityId = 3 WHERE type = 'bundle' AND 
entityId = 2

But with update statement it look out for exiting record where already that user has tour and entityID is same which we wants to update. Now in that case i want to delete that record with type bundle as i want all bundle with id 2 become tour with id 3. So if tour with id 3 exist then simply i want to delete bundle with id 2 and no need to update.

Any possible query around, please?

Upvotes: 0

Views: 28

Answers (1)

Vikash Pathak
Vikash Pathak

Reputation: 3562

Try this.

CREATE TEMPORARY TABLE IF NOT EXISTS tmp_iap AS (SELECT id, user_id, type, entityId FROM iap where type='tour' AND 
entityId = 3);


DELETE from iap where type='bundle' AND entityId = 2 AND EXISTS (SELECT 1 FROM tmp_iap Where tmp_iap.user_id = iap.user_id)

Please take a backup first if there is any important data.

Upvotes: 1

Related Questions