多表关联修改的sql语句怎么写
时间: 2023-07-01 20:18:49 浏览: 33
多表关联修改的 SQL 语句基本格式如下:
```
UPDATE table1
JOIN table2 ON table1.column = table2.column
JOIN table3 ON table2.column = table3.column
SET table1.column = value
WHERE condition;
```
其中:
- `table1`、`table2`、`table3` 等为要关联的表;
- `column` 为要关联的列;
- `value` 为要修改的值;
- `condition` 为修改条件。
例如,假设我们有两个表 `user` 和 `order`,它们的结构如下:
```
user
+----+-------+---------+
| id | name | address |
+----+-------+---------+
| 1 | Alice | Beijing |
| 2 | Bob | Shanghai|
+----+-------+---------+
order
+----+---------+-------+
| id | user_id | price |
+----+---------+-------+
| 1 | 1 | 100 |
| 2 | 2 | 200 |
+----+---------+-------+
```
现在我们想要把 `Alice` 的地址改成 `Guangzhou`,同时把她的订单价格都改成 `50`,可以使用以下 SQL 语句:
```
UPDATE user
JOIN order ON user.id = order.user_id
SET user.address = 'Guangzhou', order.price = 50
WHERE user.name = 'Alice';
```
执行后,`user` 表和 `order` 表的内容如下:
```
user
+----+-------+----------+
| id | name | address |
+----+-------+----------+
| 1 | Alice | Guangzhou |
| 2 | Bob | Shanghai |
+----+-------+----------+
order
+----+---------+-------+
| id | user_id | price |
+----+---------+-------+
| 1 | 1 | 50 |
| 2 | 2 | 200 |
+----+---------+-------+
```