mysql关联更新语法
mysql关联更新语法,跟sql server有些不同
MySQL 示例:
update landleveldata a, gdqlpj b
set a.gqdltks= b.gqdltks,
a.bztks= b.bztks
where a.GEO_Code=b.lxqdm
SQL Server示例:
update a
set a.gqdltks=b.gqdltks,a.bztks=b.bztks
from landleveldata a,gdqlpj b
where a.GEO_Code=b.lxqdm
mysql另外语法
update
modoer_subject AS s
LEFT JOIN modoer_area AS a ON s.aid = a.aid
set s.aid=642
WHERE a.pid =642
UPDATE Track
INNER JOIN MV
ON Track.trkid=MV.mvid
SET Track.is_show=MV.is_show
WHERE trkid<6
DELETE是一个蛮慎重的SQL操作,一般来说这样删除操作都需要谨慎小心,以免造成不必要的损失。
DELETE有下面这几种情况:
Ø delete from t1 where 条件
Ø delete t1 from t1 where 条件
Ø delete t1 from t1,t2 where 条件
Ø delete t1,t2 from t1,t2 where 条件
简单地说就是delete语句是无法进行多表删除数据操作,不过可以通过建立级联删除,在两个表之间建立级联删除关系,来实现删除一个表的数据时,同时删除另一个表中相关的数据。
举例说明:
1) 从数据表MV中把那些id值在数据表Track里有匹配的记录全删除掉
DELETE MV
FROM MV,Track
WHERE Track.trkid=MV.mvid
等同于
DELETE FROM MV
USING MV,Track
WHERE Track.trkid=MV.mvid
2) 从数据表MV里在数据表Track里没有匹配的记录查找出来并删除掉
DELETE MV
FROM MV
LEFT JOIN Track
ON MV.mvid=Track.trkid
WHERE Track.trkid IS NULL
等同于
DELETE FROM MV
USING MV
LEFT JOIN Track
ON MV.mvid=Track.trkid
WHERE Track.trkid IS NULL
3) 从两个表中找出相同记录的数据并把两个表中的数据都删除掉
DELETE MV,Track
FROM MV
LEFT JOIN Track
ON MV.mvid=Track.trkid
WHERE MV.mvid=1
用别名的方式:
DELETE t1,t2
FROM MV t1
LEFT JOIN Track t2
ON t1.mvid=t2.trkid
WHERE t1.mvid=2
※ 以下两种情况会报错:
delete MV,Track
from MV as t1
left join Track as t2
on t1.mvid=t2.trkid
where t1.mvid=5
***********************
DELETE t1,t2
FROM MV t1
LEFT JOIN Track t2
ON t1.mvid=t2.trkid
WHERE MV.mvid=2
so 如果使用了别名的方式,那么上下一致都用别名。