List:General Discussion« Previous MessageNext Message »
From:Paul DuBois Date:September 14 2002 12:25am
Subject:Re: Unsure of SQL Syntax
View as plain text  
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

Thread
Unsure of SQL SyntaxSteven Kreuzer13 Sep
  • re: Unsure of SQL SyntaxEgor Egorov13 Sep
  • Re: Unsure of SQL SyntaxBenjamin Pflugmann13 Sep
    • Re: Unsure of SQL SyntaxPaul DuBois14 Sep