#
# Table structure for table 'tt1'
#CREATE TABLE tt1 (
id int(11) NOT NULL default '0',
x1 char(5) NOT NULL default '0',
PRIMARY KEY (id)
) TYPE=MyISAM;#
# Dumping data for table 'tt1'
#INSERT INTO tt1 VALUES("1", "1a");
INSERT INTO tt1 VALUES("2", "2a");
INSERT INTO tt1 VALUES("3", "3a");
INSERT INTO tt1 VALUES("4", "4a");
#
# Table structure for table 'tt2'
#CREATE TABLE tt2 (
id int(11) NOT NULL default '0',
y1 char(5) NOT NULL default '0',
y2 char(5) NOT NULL default '',
PRIMARY KEY (id)
) TYPE=MyISAM;#
# Dumping data for table 'tt2'
#INSERT INTO tt2 VALUES("1", "1by1", "1by2");
INSERT INTO tt2 VALUES("2", "2by1", "2by2");
INSERT INTO tt2 VALUES("3", "3by1", "3by2");#
# 执行语句
#UPDATE tt1,tt2
SET tt1.x1 = IF(tt1.id = 1, tt2.y1, tt2.y2)
WHERE tt1.id = tt2.id
AND (tt1.id = 1 OR tt1.id = 3);
/*
测试结果mysql> SELECT * FROM tt1;
+----+----+
| id | x1 |
+----+----+
| 1 | 1a |
| 2 | 2a |
| 3 | 3a |
| 4 | 4a |
+----+----+
4 rows in set (0.00 sec)mysql> UPDATE tt1,tt2
-> SET tt1.x1 = IF(tt1.id = 1, tt2.y1, tt2.y2)
-> WHERE tt1.id = tt2.id
-> AND (tt1.id = 1 OR tt1.id = 3);
Query OK, 2 rows affected (0.00 sec)
Rows matched: 2 Changed: 2 Warnings: 0mysql> SELECT * FROM tt1;
+----+------+
| id | x1 |
+----+------+
| 1 | 1by1 |
| 2 | 2a |
| 3 | 3by2 |
| 4 | 4a |
+----+------+
4 rows in set (0.00 sec)*/
# Table structure for table 'tt1'
#CREATE TABLE tt1 (
id int(11) NOT NULL default '0',
x1 char(5) NOT NULL default '0',
PRIMARY KEY (id)
) TYPE=MyISAM;#
# Dumping data for table 'tt1'
#INSERT INTO tt1 VALUES("1", "1a");
INSERT INTO tt1 VALUES("2", "2a");
INSERT INTO tt1 VALUES("3", "3a");
INSERT INTO tt1 VALUES("4", "4a");
#
# Table structure for table 'tt2'
#CREATE TABLE tt2 (
id int(11) NOT NULL default '0',
y1 char(5) NOT NULL default '0',
y2 char(5) NOT NULL default '',
PRIMARY KEY (id)
) TYPE=MyISAM;#
# Dumping data for table 'tt2'
#INSERT INTO tt2 VALUES("1", "1by1", "1by2");
INSERT INTO tt2 VALUES("2", "2by1", "2by2");
INSERT INTO tt2 VALUES("3", "3by1", "3by2");#
# 执行语句
#UPDATE tt1,tt2
SET tt1.x1 = IF(tt1.id = 1, tt2.y1, tt2.y2)
WHERE tt1.id = tt2.id
AND (tt1.id = 1 OR tt1.id = 3);
/*
测试结果mysql> SELECT * FROM tt1;
+----+----+
| id | x1 |
+----+----+
| 1 | 1a |
| 2 | 2a |
| 3 | 3a |
| 4 | 4a |
+----+----+
4 rows in set (0.00 sec)mysql> UPDATE tt1,tt2
-> SET tt1.x1 = IF(tt1.id = 1, tt2.y1, tt2.y2)
-> WHERE tt1.id = tt2.id
-> AND (tt1.id = 1 OR tt1.id = 3);
Query OK, 2 rows affected (0.00 sec)
Rows matched: 2 Changed: 2 Warnings: 0mysql> SELECT * FROM tt1;
+----+------+
| id | x1 |
+----+------+
| 1 | 1by1 |
| 2 | 2a |
| 3 | 3by2 |
| 4 | 4a |
+----+------+
4 rows in set (0.00 sec)*/
id Status
table2结构:
id cid Time1 Time2
就是更新table2的项受Table1的Status项的值控制,当该值为1时更新Table2的Time1,该值为3时则更新Table2的Time2。
关联条件是table1.id = teble2.cid
talbe2.time1=IF(table1.status=1, value1, table2.time1)
table2.time2=IF(table1.status=2, value2, table2.time2)
WHERE table1.id=table2.cid其中,根据你的描述,我的理解是table1的status和value、table2的字段名是一一对应的
(
id int,
stat int,
UNIQUE id (id)
);INSERT INTO t1 VALUES(1, 1);
INSERT INTO t1 VALUES(2, 1);
INSERT INTO t1 VALUES(3, 2);
INSERT INTO t1 VALUES(4, 2);
INSERT INTO t1 VALUES(5, 1);CREATE TABLE t2
(
id int,
cid int,
time1 varchar(32),
time2 varchar(32),
UNIQUE id (id)
);INSERT INTO t2 VALUES(1, 1, '', '');
INSERT INTO t2 VALUES(2, 2, '', '');
INSERT INTO t2 VALUES(3, 3, '', '');
INSERT INTO t2 VALUES(4, 4, '', '');
INSERT INTO t2 VALUES(5, 5, '', '');
INSERT INTO t2 VALUES(6, 2, '', '');
INSERT INTO t2 VALUES(7, 3, '', '');update t1, t2 set
t2.time1=IF(t1.stat=1, 'aaaa', t2.time1),
t2.time2=IF(t1.stat=2, 'bbbb', t2.time2)
where t1.id=t2.cid;SELECT * FROM t2 结果为:id cid time1 time2
1 1 aaaa
2 2 aaaa
3 3 bbbb
4 4 bbbb
5 5 aaaa
6 2 aaaa
7 3 bbbb