在工作中设计到一些字段值的初始化,特别是生产系统中添加字段时处理历史数据的初始化,往往需要更新字段值,这里说说将A表中的字段值赋值给B表中的字段的几种更新方法。
1、通过子查询实现
UPDATE tableA a
SET a.column1 = (
SELECT
b.column1
FROM
tableB b
WHERE
a.id = b.aid
),
a.column2 = (
SELECT
b.column2
FROM
tableB b
WHERE
a.id = b.aid
)
WHERE
EXISTS (
SELECT
1
FROM
BtableB c
WHERE
a.id = c.aid
);
2、通过逗号操作符的内连接
UPDATE tableA a,
tableB b
SET a.column1 = b.column1,
a.column2 = b.column2
WHERE
a.id = b.aid;
3、各种内、外连接都可以实现
UPDATE tableA a
LEFT JOIN tableB b ON a.id = b.aid
SET a.column1 = b.column1,
a.column2 = b.column2;
4、SQL允许的各种连接,并根据CASE函数控制不同类型数据更新不同的值
UPDATE system_alarm s LEFT JOIN (
SELECT a.id aid,
(
CASE a.alarm_type
WHEN '1' THEN d.p1_min
WHEN '2' THEN d.p1_max
WHEN '5' THEN d.p2_min
WHEN '6' THEN d.p2_max
ELSE 0
END
) AS num
FROM system_alarm a LEFT JOIN device d ON a.device_id=d.device_id
) aaa
ON s.id=aaa.aid
SET s.threshold=aaa.num;
这里没有用伪SQL,因为结合实际业务更好理解,大概说一下这条SQL的实际业务逻辑:设备的报警表中添加了新的字段:threshold(阈值),用来保存报警时的阈值,需要根据不同的报警类型把历史报警的对应阈值放入新字段中,这里统一把当前的设备阈值保存到新字段,因修改过阈值而产生的不合理数据另外进行针对性的处理。