At 16:25 +0200 9/13/02, Benjamin Pflugmann wrote:
>Hello.
>
>On Wed 2002-09-04 at 15:09:25 -0400, skreuzer@stripped wrote:
>> I am working with a MySQL database and I am hoping someone can help me
>> out with this.
>>
>> CREATE TABLE CLASS (
>> CID int(22) NOT NULL auto_increment,
>> LOC int(11) default NULL,
>> CLI int(8) NOT NULL default '0',
>> TYPE tinyint(4) NOT NULL default '0',
>> STATUS char(3) NOT NULL default '',
>> UID int(22) NOT NULL);
>>
>> CREATE TABLE GROUP (
>> GRID int(11) NOT NULL auto_increment,
>> NAME varchar(40) NOT NULL default '',
>> MAXACT int(11) NOT NULL default '0',
>> LEVEL int(11) NOT NULL default '0',
>> ADMIN int(22) NOT NULL default '');
>>
>> Now I need to update the class table:
>>
>> set the CLASS.UID field to the `GROUP`.ADMIN field (join them on
>> CLASS.CLI=`GROUP`.GRID) if CLASS.UID=2 and `GROUP`.GRID<>1.
>>
>> What would that SQL syntax look like?
>
>MySQL does not support cross-table updates. You have to do a seperate
>SELECT and an UPDATE. Multi-table updates are planned for MySQL 4.1,
>IIRC.
Actually, they're available now (in 4.0.3). Here's a simple example:
Create a couple of tables and insert some data:
mysql> CREATE TABLE t1 (i INT, j INT);
mysql> CREATE TABLE t2 (i INT, j INT);
mysql> INSERT INTO t1 SET i = 1, j = 0;
mysql> INSERT INTO t1 SET i = 2, j = 0;
mysql> INSERT INTO t2 SET i = 1, j = 4;
mysql> SELECT * FROM t1;
+------+------+
| i | j |
+------+------+
| 1 | 0 |
| 2 | 0 |
+------+------+
mysql> SELECT * FROM t2;
+------+------+
| i | j |
+------+------+
| 1 | 4 |
+------+------+
1 row in set (0.01 sec)
Update rows in t1 that match rows in t2:
mysql> UPDATE t1, t2 SET t1.j = t2.j WHERE t1.i = t2.i;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0
See what happened:
mysql> SELECT * FROM t1;
+------+------+
| i | j |
+------+------+
| 1 | 4 |
| 2 | 0 |
+------+------+
Only the row with a matching i value has had the j value updated.
>
>HTH,
>
> Benjamin.
>
>--
>benjamin-mysql@stripped
