update games set games.status = 2 inner join gameapplyways
on games.id = gameapplyways.game_id
where gameapplyways.apply_deadline < curdate()
and games.status = 7 and games.id = 6
这句SQL老是提示错误。为什么啊?
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'inner join gameapplyways
on games.id = gameapplyways.game_id
where gameapplyw' at line 1
update games set games.status = 2
from games inner join gameapplyways
on games.id = gameapplyways.game_id
where gameapplyways.apply_deadline < curdate()
and games.status = 7 and games.id = 6
where b.apply_deadline<getdate() and a.status=7 and a.id=6
from games a
join gameapplyways b on a.id = b.game_id
where b.apply_deadline < curdate()
and a.status = 7 and a.id = 6
and games.status = 7 and games.id = 6
update games,gameapplyways set games.status = 2
where games.id = gameapplyways.game_id and
gameapplyways.apply_deadline < curdate() and
games.status = 7 and games.id = 6
where games.id in (select game_id from gameapplyways apply_deadline < curdate() )
and games.status = 7 and games.id = 6
where games.status = 7 and games.id = 6
set games.status = 2
where gameapplyways.apply_deadline < curdate()
and games.status = 7 and games.id = 6
mysql> use tempdb
Database changedmysql> drop table cl1,cl2;
Query OK, 0 rows affected (0.03 sec)mysql> create table cl1 (a int,b datetime);
Query OK, 0 rows affected (0.07 sec)mysql> insert into cl1 select 1,curdate();
Query OK, 1 row affected (0.00 sec)
Records: 1 Duplicates: 0 Warnings: 0mysql> insert into cl1 select 1,curdate()-1;
Query OK, 1 row affected (0.00 sec)
Records: 1 Duplicates: 0 Warnings: 0mysql> create table cl2 (a int ,b int);
Query OK, 0 rows affected (0.07 sec)mysql> insert into cl2 select 2,11;
Query OK, 1 row affected (0.00 sec)
Records: 1 Duplicates: 0 Warnings: 0mysql> select * from cl1;
+------+---------------------+
| a | b |
+------+---------------------+
| 1 | 2010-09-09 00:00:00 |
| 1 | 2010-09-08 00:00:00 |
+------+---------------------+
2 rows in set (0.00 sec)mysql> select * from cl2;
+------+------+
| a | b |
+------+------+
| 1 | 11 |
| 2 | 11 |
+------+------+
2 rows in set (0.00 sec)mysql> update cl1 set a =2 where b =curdate();
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0mysql> select * from cl1;
+------+---------------------+
| a | b |
+------+---------------------+
| 2 | 2010-09-09 00:00:00 |
| 1 | 2010-09-08 00:00:00 |
+------+---------------------+
2 rows in set (0.00 sec)mysql> select * from cl2;
+------+------+
| a | b |
+------+------+
| 1 | 11 |
| 2 | 11 |
+------+------+
2 rows in set (0.00 sec)mysql> update cl1,cl2 set cl2.b=22 where cl1.a=cl2.a and cl1.b < curdate();
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0mysql> select * from cl1;
+------+---------------------+
| a | b |
+------+---------------------+
| 2 | 2010-09-09 00:00:00 |
| 1 | 2010-09-08 00:00:00 |
+------+---------------------+
2 rows in set (0.00 sec)mysql> select * from cl2;
+------+------+
| a | b |
+------+------+
| 1 | 22 |
| 2 | 11 |
+------+------+
2 rows in set (0.00 sec)mysql>