You can do left joins in an update.
mysql> show create table t;
+-------+-------------------------------------------------------------------
------
| Table | Create Table
+-------+-------------------------------------------------------------------
------
| t | CREATE TABLE `t` (
`key1` int(11) NOT NULL default '0',
`key2` int(11) NOT NULL default '0',
PRIMARY KEY (`key1`,`key2`)
) TYPE=MyISAM |
+-------+-------------------------------------------------------------------
------
1 row in set (0.01 sec)
mysql> insert into t values
(5,5),(20,25),(10,15),(10,20),(10,30),(15,20),(20,30);
Query OK, 7 rows affected (0.00 sec)
Records: 7 Duplicates: 0 Warnings: 0
mysql> select * from t;
+------+------+
| key1 | key2 |
+------+------+
| 5 | 5 |
| 10 | 15 |
| 10 | 20 |
| 10 | 30 |
| 15 | 20 |
| 20 | 25 |
| 20 | 30 |
+------+------+
7 rows in set (0.00 sec)
mysql> update t as t1
-> left join t as t2
-> on (t1.key2 = t2.key2
-> and t2.key1 = 20)
-> set t1.key1 = 20
-> where t2.key1 IS NULL
-> AND t1.key1 = 10;
Query OK, 2 rows affected (0.00 sec)
Rows matched: 2 Changed: 2 Warnings: 0
mysql> select * from t;
+------+------+
| key1 | key2 |
+------+------+
| 5 | 5 |
| 10 | 30 |
| 15 | 20 |
| 20 | 15 |
| 20 | 20 |
| 20 | 25 |
| 20 | 30 |
+------+------+
-----Original Message-----
From: Bob Dankert [mailto:[EMAIL PROTECTED]
Sent: Wednesday, March 16, 2005 2:27 PM
To: [email protected]
Subject: Trouble performing an update
I am trying to update a link table (table with two primary keys) where I
want to update all rows where the first primary key is a set value (for
example, change key1 from 10 to 20), but I only want to update these
where the resulting primary key does not already exist in the table
(otherwise an error is thrown on a duplicate key and the remaining rows
are not updated). Using other databases, I am able to perform a
subquery in the filter for the update such as the following:
UPDATE mytable SET key1 = 20 WHERE key2 NOT IN (SELECT key2 FROM mytable
WHERE key1 = 20)
Unfortunately, MySQL does not allow you to use a table in a subquery
which is being updated. If anyone can offer any assistance with this, I
would greatly appreciate it.
Thanks,
Bob Dankert
Envision Information Technologies
Associate
[EMAIL PROTECTED]
v. 608.256.5680
f. 608.256.3780
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
