Our website has listings. We use a connections table with the following structure to connect our members with these various listings:
CREATE TABLE `connections` (
`cid1` int(9) unsigned NOT NULL DEFAULT '0',
`cid2` int(9) unsigned NOT NULL DEFAULT '0',
`type` char(2) NOT NULL,
`created` datetime DEFAULT NULL,
`updated` datetime DEFAULT NULL,
PRIMARY KEY (`cid1`,`cid2`,`type`,`cid3`),
KEY `cid1` (`cid1`,`type`),
KEY `cid2` (`cid2`,`type`)
);
The problem we've run into is when we have to combine duplicate listings from time to time we also need to update our member connections and have been using the following query which breaks if a member is connected to both listings:
update connections set cid2=100000
where type IN ('MC','MT','MW') AND cid2=100001;
What I can't figure out is how to do the following which would solve this issue:
update connections set cid2=100000
where type IN ('MC','MT','MW') AND cid2=100001 AND cid1 NOT IN (
select cid1 from connections
where type IN ('MC','MT','MW') AND cid2=100000
);
When I try to run that query I get the following error:
ERROR 1093 (HY000): You can't specify target table 'connections' for update in FROM clause
Here is some sample data. Notice the update conflict for cid1 = 10025925
+----------+--------+------+---------------------+---------------------+
| cid1 | cid2 | type | created | updated |
+----------+--------+------+---------------------+---------------------+
| 10010388 | 100000 | MC | 2010-08-05 18:04:51 | 2011-06-16 16:26:17 |
| 10025925 | 100000 | MC | 2010-10-31 09:21:25 | 2010-10-31 16:21:25 |
| 10027662 | 100000 | MC | 2011-06-13 16:31:12 | NULL |
| 10038375 | 100000 | MW | 2011-02-05 05:32:35 | 2011-02-05 19:51:58 |
| 10065771 | 100000 | MW | 2011-04-24 17:06:35 | NULL |
| 10025925 | 100001 | MC | 2010-10-31 09:21:45 | 2010-10-31 16:21:45 |
| 10034884 | 100001 | MC | 2011-01-20 18:54:51 | NULL |
| 10038375 | 100001 | MC | 2011-02-04 05:00:35 | NULL |
| 10041989 | 100001 | MC | 2011-02-26 09:33:18 | NULL |
| 10038259 | 100001 | MC | 2011-05-07 13:34:20 | NULL |
| 10027662 | 100001 | MC | 2011-06-13 16:33:54 | NULL |
| 10030855 | 100001 | MT | 2010-12-31 20:40:18 | NULL |
| 10038375 | 100001 | MT | 2011-02-04 05:00:36 | NULL |
+----------+--------+------+---------------------+---------------------+
I'm hoping that someone can suggest the right way to run the above query. Thanks in advance!
The reason for the error in your query is because in MySQL you cannot SELECT from the table you are trying to UPDATE in the same query.
Use UPDATE IGNORE to avoid duplicate conflicts.
I think you should try reading INSERT ON DUPLICATE KEY. The idea is that you frame an INSERT query that always create a DUPLICATE conflict and then the UPDATE part will do its part.