使用MySQL 4.0版或更高更新您可以通过加入两个或多个表一起一表;通过加入两个表连同您可以更新一个表的记录在相关领域的总部设在另一个表。
先来几个简单的示例
Solution 1: 1列
1
2
3
|
update
student s, city c
set
s.city_name = c.
name
where
s.city_code = c.code;
|
Solution 2: 多个列
1
2
3
4
|
update
a, b
set
a.title=b.title, a.
name
=b.
name
where
a.id=b.id
|
Solution 3: 子查询
1
|
update
student s
set
city_name = (
select
name
from
city
where
code = s.city_code);
|
我们再来看几个负责写的
例如: 把表 tk_zyt_scenery_order的 字段更新到 t_advs_order中去, 一般可能会这样写:
1
2
3
4
5
6
7
|
UPDATE
t_advs_order
SET
attribute1=(
SELECT
o.order_state
FROM
tk_zyt_scenery_order o
WHERE
o.order_id=`
on
`),
attribute2=(
SELECT
o.order_state
FROM
tk_zyt_scenery_order o
WHERE
o.order_id=`
on
`)
WHERE
EXISTS (
SELECT
o.order_state
FROM
tk_zyt_scenery_order o
WHERE
o.order_id=`
on
`);
|
这样效率比较低下, 优化写法:
1
2
3
4
5
|
UPDATE
t_advs_order a
INNER
JOIN
tk_zyt_scenery_order s
ON
s.order_id=a.`
on
`
SET
a.attribute1=s.order_id,
a.attribute2=s.order_id;
|