今天在应用时突然遇到这样的sql,把两个表的记录整合在一个表中,问题基本如下:
把record1表中的age字段整合到 record表中的age表中,想来半天也不知道如何下手?
希望有人能帮忙解决,最好是写sql,不要用PL/SQLL!mysql> select * from record;
+----+----------+------+
| id | name | age |
+----+----------+------+
| 1 | kevin | NULL |
| 2 | longling | NULL |
+----+----------+------+
2 rows in set (0.00 sec)mysql> select * from record1;
+----+-----+
| id | age |
+----+-----+
| 1 | 22 |
| 2 | 21 |
+----+-----+结果应该如下:
mysql> select * from record;
+----+----------+------+
| id | name | age |
+----+----------+------+
| 1 | kevin | 22 |
| 2 | longling | 21 |
+----+----------+------+
2 rows in set (0.00 sec)
把record1表中的age字段整合到 record表中的age表中,想来半天也不知道如何下手?
希望有人能帮忙解决,最好是写sql,不要用PL/SQLL!mysql> select * from record;
+----+----------+------+
| id | name | age |
+----+----------+------+
| 1 | kevin | NULL |
| 2 | longling | NULL |
+----+----------+------+
2 rows in set (0.00 sec)mysql> select * from record1;
+----+-----+
| id | age |
+----+-----+
| 1 | 22 |
| 2 | 21 |
+----+-----+结果应该如下:
mysql> select * from record;
+----+----------+------+
| id | name | age |
+----+----------+------+
| 1 | kevin | 22 |
| 2 | longling | 21 |
+----+----------+------+
2 rows in set (0.00 sec)
update record r1 inner join record1 r2 on r1.id=r2.id
set r1.age=r2.age
set record.age=record1.age;
set r1.age=r2.age