I have the following update query which runs when a user logs in to their account having place items in their cart prior to logging in (a temporary account set up on the table)...
UPDATE cart_items SET account_id=$account WHERE account_id=$cookieId;
This occasionally creates duplicate results looking something like this:
id | account_id | itemNumber | itemQuantity
------------------------------------------
20 | 10 | 6 | 2
25 | 10 | 6 | 1
What I would like to do i write a query which avoids creating these duplicate records and just leaves a single record like this:
id | account_id | itemNumber | itemQuantity
------------------------------------------
20 | 10 | 6 | 3
I think using DUPLICATE KEY UPDATE
might be what I'm looking for but I can't get my head around it. Can anyone help me out please?