MySQL UPDATE conflicts with unique key

The table is as follows:

CREATE TABLE `ToursCartsItems` (
  `Id` int(10) unsigned NOT NULL auto_increment,
  `UserId` char(40) default NULL,
  `TourId` int(10) unsigned NOT NULL,
  `CreatedAt` int(10) unsigned default NULL,
  PRIMARY KEY  (`Id`),
  UNIQUE KEY `UniqueUserProduct` (`UserId`,`TourId`)
) ENGINE=MyISAM AUTO_INCREMENT=5 DEFAULT CHARSET=latin1 ROW_FORMAT=FIXED

// simple sample data
INSERT INTO
    ToursCartsItems (UserId, TourId)
VALUES
    ("old", 1), ("old", 2), ("new", 1), ("new", 3);

So one user can own many tours (never mind what tours are). The UserId field is a char, not an int, because the user may not be logged in, in which case the session ID is used.

When a user logs in, their UserID changes. So the simple update开发者_如何转开发 would be

UPDATE ToursCartsItems SET UserId="new" WHERE UserId="old"
-- In reality, the new UserId would be an integer, but never mind that.

But this might give a duplicate entry key. Where both the old user and the new user have the same tour, we should just drop one before updating.

So I’ve tried

UPDATE ToursCartsItems
    SET UserId="in"
    WHERE UserId="out"
    AND (TourId NOT IN (SELECT TourId FROM ToursCartsItems WHERE UserId="in")
);
DELETE FROM ToursCartsItems WHERE UserId="old";

and

TRUNCATE ToursCartsItems;
INSERT INTO ToursCartsItems (UserId, TourId) VALUES ("old", 1), ("old", 2), ("new", 1), ("new", 3);
DELETE FROM ToursCartsItems WHERE UserId="old" AND TourId IN (SELECT TourId FROM ToursCartsItems WHERE UserId="new");
UPDATE ToursCartsItems SET UserId="new" WHERE UserId="old";

Both give me errors. Is there any way to do this in the SQL query itself, or do I just have to do

SELECT * FROM ToursCartsItems WHERE UserId IN ("old", "new")

and then do the necessary calculations myself in PHP?

I guess what you are trying to do is first remove the conflicting records and then insert the new record. This might help...

mysql> select * from ToursCartsItems;
+----+--------+--------+-----------+
| Id | UserId | TourId | CreatedAt |
+----+--------+--------+-----------+
|  1 | old    |      1 |      NULL | 
|  2 | old    |      2 |      NULL | 
|  3 | new    |      1 |      NULL | 
|  4 | new    |      3 |      NULL | 
+----+--------+--------+-----------+
4 rows in set (0.00 sec)

mysql> delete from b using ToursCartsItems as a inner join ToursCartsItems as b on a.TourId = b.TourId where a.UserId = 'new' and b.UserId = 'old' and a.Id <> b.Id;

mysql> select * from ToursCartsItems;
+----+--------+--------+-----------+
| Id | UserId | TourId | CreatedAt |
+----+--------+--------+-----------+
|  2 | old    |      2 |      NULL | 
|  3 | new    |      1 |      NULL | 
|  4 | new    |      3 |      NULL | 
+----+--------+--------+-----------+
3 rows in set (0.00 sec)

I may be wrong, but this sounds like a Shopping Cart scenario to me. Why not store anonymous and logged in tours in separate tables? I.e., after the user logs in, convert the anonymous "Shopping Cart" tours into actual tour "Orders" in another table?

That way you are not destroying any data. Besides, how do you know that the person who just logged in is the same as the person who added the tours before they logged in?