删除，用派生表更新? | 用派生表更新

本文介绍了删除，用派生表更新?的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

我刚刚在mysql中研究了FROM子句和derived tables，大多数网站都使用SELECT命令提供了示例

I have just studied FROM clause and derived tables in mysql and most of the websites provided the examples using SELECT command

示例SELECT * FROM (SELECT * FROM usrs) as u WHERE u.name = 'john'

但是当我尝试使用delete或update命令时，它似乎不起作用.

But when I have tried using delete or update command it does not seem to work.

示例DELETE FROM (SELECT * FROM usrs) as u WHERE u.name = 'john'

1064-您的SQL语法有误；检查与您的MySQL服务器版本相对应的手册以获取正确的语法，以在u WHERE u.name ='john'行附近使用(SELECT * FROM usrs)

UPDATE (SELECT * FROM usrs) as u SET u.lname ='smith' WHERE u.name = 'john'

1288 UPDATE的目标表e无法更新

因此派生表不能与delete或update命令一起使用?还是有办法使其工作.

So derived tables does not work with delete or update commands? or is there a way to make it work.

不是要编写用于更新和删除的表名，而是要编写一个获取记录并对该记录执行删除操作的子查询?在mysql中有可能吗?

Instead of writing the table name for update and delete I want to write a subquery that gets the records and perform the delete operation on that records? Is that possible in mysql?

已更新，我必须删除一条记录，并且我有三个表，该记录可能存在于任何表中

UPDATED I have to delete a record and i have three tables, the record may exist in any of the table

我的方法delete from first table rows effected? quit: else check second table rows effected? quit : else check third table

但是如果我使用 UNION ALL ，我可以这样做

But if I use UNION ALL I can do this way

Delete from (select * from tb1 union all select * from tb2 union all select * from tb3) e as e.uname = 'john'

但是此查询似乎不起作用，现在谁能告诉我在要搜索多个表的情况下如何删除或更新记录.任何帮助是极大的赞赏.

but this query does not seem to work , now could anyone tell me how do i delete or update a record when i have more than one table to search. Any help is greatly appreciated.

推荐答案

您不能直接从子查询中删除，但是如果愿意，您仍然可以使用它，您只需要在JOIN:

You can't directly delete from the subquery, but you can still use it if you'd like, you'll just need to use it in a JOIN:

DELETE usrs
FROM usrs
    INNER JOIN (
        SELECT * FROM usrs WHERE name = 'john'
    ) t ON usrs.Id = t.Id

或者您可以使用IN:

DELETE usrs
WHERE ID IN (
   SELECT ID
   FROM usrs
   WHERE name = 'John'
)

这样说，在这个例子中，我不知道为什么你想要一个子查询:

With this said, for this example, I don't know why you'd want a subquery:

DELETE usrs WHERE name = 'John'

根据评论进行编辑.要同时从多个表中删除，您可以具有多个DELETE语句，也可以使用以下内容:

Edit base on comments. To delete from multiple tables at the same time, you can either have multiple DELETE statements, or you can use something like the following:

delete t1, t2, t3
from (select 'john' as usr) t
  left join t1 on t.usr=t1.usr
  left join t2 on t.usr=t2.usr
  left join t3 on t.usr=t3.usr

SQL小提琴演示

SQL Fiddle Demo

这篇关于删除，用派生表更新?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！