update tb A,tb B set A.name = B.name where A.id = B.id +5
mysql> select * from a; +------+------+ | id | name | +------+------+ | 1 | A | | 2 | B | | 3 | C | | 4 | D | | 5 | E | | 6 | F | | 7 | G | | 8 | H | | 9 | I | | 10 | J | +------+------+ 10 rows in set (0.00 sec)mysql> update a p inner join a q on p.id=q.id+5 -> set p.name=q.name -> where p.id between 6 and 10; Query OK, 5 rows affected (0.01 sec) Rows matched: 5 Changed: 5 Warnings: 0mysql> select * from a; +------+------+ | id | name | +------+------+ | 1 | A | | 2 | B | | 3 | C | | 4 | D | | 5 | E | | 6 | A | | 7 | B | | 8 | C | | 9 | D | | 10 | E | +------+------+ 10 rows in set (0.00 sec)mysql>
127.0.0.1~root@localhost~test>create table A(id int,name varchar(30)); Query OK, 0 rows affected (0.11 sec)127.0.0.1~root@localhost~test>insert into A values(1,'A'),(2,'B'),(3,'C'),(4,'D'),(5,'E') ,'J'); Query OK, 10 rows affected (0.02 sec) Records: 10 Duplicates: 0 Warnings: 0127.0.0.1~root@localhost~test>select * from A; +------+------+ | id | name | +------+------+ | 1 | A | | 2 | B | | 3 | C | | 4 | D | | 5 | E | | 6 | F | | 7 | G | | 8 | H | | 9 | I | | 10 | J | +------+------+ 10 rows in set (0.00 sec)127.0.0.1~root@localhost~test>update A i,A j set i.name=j.name where a.id=j.id+5; ERROR 1054 (42S22): Unknown column 'a.id' in 'where clause' 127.0.0.1~root@localhost~test>update A i,A j set i.name=j.name where i.id=j.id+5; Query OK, 5 rows affected (0.00 sec) Rows matched: 5 Changed: 5 Warnings: 0127.0.0.1~root@localhost~test>select * from A; +------+------+ | id | name | +------+------+ | 1 | A | | 2 | B | | 3 | C | | 4 | D | | 5 | E | | 6 | A | | 7 | B | | 8 | C | | 9 | D | | 10 | E | +------+------+ 10 rows in set (0.00 sec)127.0.0.1~root@localhost~test>
set A.name = B.name
where A.id = B.id +5
+------+------+
| id | name |
+------+------+
| 1 | A |
| 2 | B |
| 3 | C |
| 4 | D |
| 5 | E |
| 6 | F |
| 7 | G |
| 8 | H |
| 9 | I |
| 10 | J |
+------+------+
10 rows in set (0.00 sec)mysql> update a p inner join a q on p.id=q.id+5
-> set p.name=q.name
-> where p.id between 6 and 10;
Query OK, 5 rows affected (0.01 sec)
Rows matched: 5 Changed: 5 Warnings: 0mysql> select * from a;
+------+------+
| id | name |
+------+------+
| 1 | A |
| 2 | B |
| 3 | C |
| 4 | D |
| 5 | E |
| 6 | A |
| 7 | B |
| 8 | C |
| 9 | D |
| 10 | E |
+------+------+
10 rows in set (0.00 sec)mysql>
127.0.0.1~root@localhost~test>create table A(id int,name varchar(30));
Query OK, 0 rows affected (0.11 sec)127.0.0.1~root@localhost~test>insert into A values(1,'A'),(2,'B'),(3,'C'),(4,'D'),(5,'E')
,'J');
Query OK, 10 rows affected (0.02 sec)
Records: 10 Duplicates: 0 Warnings: 0127.0.0.1~root@localhost~test>select * from A;
+------+------+
| id | name |
+------+------+
| 1 | A |
| 2 | B |
| 3 | C |
| 4 | D |
| 5 | E |
| 6 | F |
| 7 | G |
| 8 | H |
| 9 | I |
| 10 | J |
+------+------+
10 rows in set (0.00 sec)127.0.0.1~root@localhost~test>update A i,A j set i.name=j.name where a.id=j.id+5;
ERROR 1054 (42S22): Unknown column 'a.id' in 'where clause'
127.0.0.1~root@localhost~test>update A i,A j set i.name=j.name where i.id=j.id+5;
Query OK, 5 rows affected (0.00 sec)
Rows matched: 5 Changed: 5 Warnings: 0127.0.0.1~root@localhost~test>select * from A;
+------+------+
| id | name |
+------+------+
| 1 | A |
| 2 | B |
| 3 | C |
| 4 | D |
| 5 | E |
| 6 | A |
| 7 | B |
| 8 | C |
| 9 | D |
| 10 | E |
+------+------+
10 rows in set (0.00 sec)127.0.0.1~root@localhost~test>