pgsql 删除一张表数据，条件是多表联合

最新推荐文章于 2025-09-29 00:26:42 发布

原创最新推荐文章于 2025-09-29 00:26:42 发布 · 1.2k 阅读

2 ·

CC 4.0 BY-SA版权

文章标签：

#数据库 #mysql

本文展示了如何使用SQL进行复杂的多表操作，包括使用USING关键字删除指定条件的数据，以及结合INNERJOIN和LEFTJOIN进行表更新。示例涉及了基于几何数据的查询条件和特定版本的更新规则。

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'