寫sql的功夫低啊,得慢慢提高才行

bulletming發表於2019-05-22

今天看到一個,然後嘗試一下

表連線後做update


mysql> select * from b;
+---------------------+------+------+
| Time | id | test |
+---------------------+------+------+
| 2007-08-11 21:15:07 | 1 | NULL |
| 2007-08-11 21:15:09 | 2 | NULL |
| 2007-08-11 21:15:11 | 3 | NULL |
| 2007-08-13 19:49:32 | 4 | NULL |
| 2007-08-13 19:55:32 | NULL | NULL |
+---------------------+------+------+
5 rows in set (0.00 sec)

mysql> select * from a;
+---+------+------+
| a | c | id |
+---+------+------+
| 1 | 2 | 3 |
| 3 | 4 | 4 |
+---+------+------+
2 rows in set (0.00 sec)

mysql> update b inner join ( select * from a ) using (id) set test = 1;
ERROR 1248 (42000): Every derived table must have its own alias
mysql> update b inner join ( select * from a ) as chosen using (id) set test = 1;
Query OK, 2 rows affected (0.00 sec)
Rows matched: 2 Changed: 2 Warnings: 0

mysql> select * from b;
+---------------------+------+------+
| Time | id | test |
+---------------------+------+------+
| 2007-08-11 21:15:07 | 1 | NULL |
| 2007-08-11 21:15:09 | 2 | NULL |
| 2007-11-15 11:49:39 | 3 | 1 |
| 2007-11-15 11:49:39 | 4 | 1 |
| 2007-08-13 19:55:32 | NULL | NULL |
+---------------------+------+------+
5 rows in set (0.00 sec)

直接像postgresql好像也可以,記得oracle不行的

mysql> select * from b;
+---------------------+------+------+
| Time | id | test |
+---------------------+------+------+
| 2007-08-11 21:15:07 | 1 | NULL |
| 2007-08-11 21:15:09 | 2 | NULL |
| 2007-11-15 11:49:39 | 3 | 1 |
| 2007-11-15 11:49:39 | 4 | 1 |
| 2007-08-13 19:55:32 | NULL | NULL |
+---------------------+------+------+
5 rows in set (0.00 sec)

mysql> update b,a set test = 0 where b.id = a.c;
Query OK, 2 rows affected (0.00 sec)
Rows matched: 2 Changed: 2 Warnings: 0

mysql> select * from b;
+---------------------+------+------+
| Time | id | test |
+---------------------+------+------+
| 2007-08-11 21:15:07 | 1 | NULL |
| 2007-11-15 11:54:19 | 2 | 0 |
| 2007-11-15 11:49:39 | 3 | 1 |
| 2007-11-15 11:54:19 | 4 | 0 |
| 2007-08-13 19:55:32 | NULL | NULL |
+---------------------+------+------+
5 rows in set (0.00 sec)

來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/82392/viewspace-144727/,如需轉載,請註明出處,否則將追究法律責任。

相關文章