1. delete删除指定表数据,条件是多表关联查询 使用关键字:USING
DELETE
FROM
ics_check_misreport t1 USING ics_check_item t2
WHERE
t1.chk_id = t2.chk_id
AND t2.imp_level = 'S'
AND st_intersects ( 'MULTIPOLYGON Z (((119.146539847 29.266160672 104.327810735,119.147730849 29.267754088 110.982317651,119.147564803 29.269530686 116.539,119.148009726 29.271646475 121.7279,119.149970541 29.27535256 106.3257,119.152265233 29.277646223 116.9627,119.155660822 29.280874325 107.272217477,119.160814295 29.287016282 98.656636428,119.163883027 29.290240282 125.169836711,119.165437051 29.293187269 111.795152975,119.16663821 29.297902079 63.443199754,119.166463238 29.301505417 86.5921,119.164543012 29.308078876 110.7097,119.163203569 29.312422515 116.3266,119.162642948 29.315890256 91.3487,119.16293946 29.320160384 107.143009732,119.164208865 29.324562331 93.920710157,119.165330606 29.327082 78.808910917)))', st_astext ( t1.geom ) );
2. 更新一张表,条件是多表关联,使用再加上inner join 或 left join
UPDATE ics_check_misreport t1
SET t1.chk_version = '20230705'
FROM
ics_check_item t2
INNER JOIN ics_check_report t3 ON t3.chk_id = t2.chk_id
WHERE
t1.chk_id = t2.chk_id
AND t2.imp_level = 'S'
AND t1.chk_version = 'V1.3'
2.两个表联合更新
UPDATE ics_check_misreport t1
SET t1.chk_version = '20230705'
FROM
ics_check_item t2
WHERE
t1.chk_id = t2.chk_id
AND t2.imp_level = 'S'
AND t1.chk_version = 'V1.3'