SQL Postgresql 从多个表中删除多行
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/33699480/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Postgresql delete multiple rows from multiple tables
提问by fawzib
Consider 2 or more tables:
考虑 2 个或更多表：
users (id, firstname, lastname)
orders (orderid, userid, orderdate, total)
I wish to delete all usersand their ordersthat match first name 'Sam'. In mysql, I usually do left join. In this example userid is unknown to us.
我希望删除与名字“ Sam”匹配的所有用户及其订单。在mysql中，我通常做左连接。在这个例子中，我们不知道用户 ID。
What is the correct format of the query?
查询的正确格式是什么？
采纳答案by Juan Carlos Oropeza
http://www.postgresql.org/docs/current/static/sql-delete.html
http://www.postgresql.org/docs/current/static/sql-delete.html
DELETE
FROM orders o
USING users u
WHERE o.userid = u.id
and u.firstname = 'Sam';
DELETE
FROM users u
WHERE u.firstname = 'Sam';
You can also create the table with ON delete cascade
您还可以创建表 ON delete cascade
http://www.postgresql.org/docs/current/static/ddl-constraints.html
http://www.postgresql.org/docs/current/static/ddl-constraints.html
CREATE TABLE order_items (
product_no integer REFERENCES products ON DELETE RESTRICT,
order_id integer REFERENCES orders ON DELETE CASCADE,
quantity integer,
PRIMARY KEY (product_no, order_id)
);
回答by Chaos Crafter
Arranging proper cascading deletes is wise and is usually the correct solution to this. For certain special cases, there is another solution to this that can be relevant.
安排适当的级联删除是明智的，通常是解决此问题的正确方法。对于某些特殊情况，还有另一种相关的解决方案。
If you need to perform multiple deletes based on a common set of data you can use CTEs
如果您需要根据一组通用数据执行多次删除，您可以使用 CTE
It's hard to come up with a simple example as the main use case for this can be covered by cascading deletes.
很难想出一个简单的例子，因为级联删除可以覆盖这个的主要用例。
For the example we're going to delete all items in table A whose value is in the set of values we're deleting from table B. Usually these would be keys, but where they are not, then cascading delete can't be used.
例如，我们将删除表 A 中的所有项目，其值在我们要从表 B 中删除的一组值中。通常这些是键，但如果不是，则不能使用级联删除.
To solve this you use CTEs
为了解决这个问题，你使用 CTE
WITH Bdeletes AS (
DELETE from B where IsSomethingToDelete = true returning ValueThatRelatesToA
)
delete from A where RelatedValue in (select ValueThatRelatesToA from Bdeletes)
This example is deliberately simple because my point is not to argue over key mapping etc, but to show how two or more deletes can be performed off a shared dataset. This can be much more complex too, including update commands etc.
这个例子故意简单，因为我的观点不是争论键映射等，而是展示如何对共享数据集执行两个或多个删除。这也可能更复杂，包括更新命令等。
Here is a more complex example (from Darth Vader's personal database). In this case, we have a table that references an address table. We need to delete addresses from the address table if they are in his list of planets he's destroyed. We want to use this information to delete from the people table, but only if they were on-planet (or on his trophy-kill list)
这是一个更复杂的示例（来自 Darth Vader 的个人数据库）。在这种情况下，我们有一个引用地址表的表。如果地址在他摧毁的行星列表中，我们需要从地址表中删除地址。我们想用这个信息从 people 表中删除，但前提是他们在星球上（或在他的奖杯杀死名单上）
with AddressesToDelete as (
select AddressId from Addresses a
join PlanetsDestroyed pd on pd.PlanetName = a.PlanetName
),
PeopleDeleted as (
delete from People
where AddressId in (select * from AddressesToDelete)
and OffPlanet = false
and TrophyKill = false
returning Id
),
PeopleMissed as (
update People
set AddressId=null, dead=(OffPlanet=false)
where AddressId in (select * from AddressesToDelete)
returning id
)
Delete from Addresses where AddressId in (select * from AddressesToDelete)
Now his database is up to date. No integrity failures due to Address deletion. Note that while we are returning data from the update and the first delete, it doesn't mean we have to use it. I'm uncertain whether you can put a delete in a CTE with no returned data (My SQL may also be wrong on the use of returning from an update - I've not been able to test run this as Darth V. was in a cranky mood.
现在他的数据库是最新的。由于地址删除没有完整性失败。请注意，虽然我们从更新和第一次删除返回数据，但这并不意味着我们必须使用它。我不确定您是否可以在没有返回数据的 CTE 中进行删除（我的 SQL 在使用从更新返回时也可能是错误的 - 我无法测试运行它，因为 Darth V. 在脾气暴躁。
回答by klin
Define useridas a foreign key to users (id)with cascading delete, e.g.:
使用级联删除定义userid为外键users (id)，例如：
create table users (
id int primary key,
firstname text,
lastname text);
create table orders (
orderid int primary key,
userid int references users (id) on delete cascade,
orderdate date,
total numeric);
delete from users
where firstname = 'Sam';
