更新一个表加入另外一个表.
UPDATE t1 SET t1.col1 =1 FROM table1 t1 JOIN table2 t2
ON t1.ID=t2.ID
WHERE t1.Name='Test' AND t2.Age=25;
我收到此错误,您的sql语法中有错误;查看与您的MysqL服务器版本对应的手册,以便在’FROM table1 t1 JOIN table2 t2附近使用正确的语法…
有什么想法吗？
谢谢.
最佳答案
UPDATE语句中不应该有FROM子句,SET子句应该遵循完整的表引用集：
UPDATE table1 t1
JOIN table2 t2 ON t1.ID = t2.ID
SET t1.col1 = 1
WHERE t1.Name = 'Test' AND t2.Age = 25;
测试用例：
CREATE TABLE table1 (id int,col1 int,name varchar(20));
CREATE TABLE table2 (id int,age int);
INSERT INTO table1 VALUES (1,'Test');
INSERT INTO table1 VALUES (2,'Test');
INSERT INTO table1 VALUES (3,'No Test');
INSERT INTO table2 VALUES (1,20);
INSERT INTO table2 VALUES (2,25);
INSERT INTO table2 VALUES (3,25);
结果：
SELECT * FROM table1;
+------+------+---------+
| id | col1 | name |
+------+------+---------+
| 1 | 0 | Test |
| 2 | 1 | Test |
| 3 | 0 | No Test |
+------+------+---------+
3 rows in set (0.00 sec)