从另一个数据库中选择删除数据 [英] Deleting data with select from another database

问题描述

我想从一个数据库中删除数据并从另一个数据库中选择.

I want to delete data from one database with select from another one.

delete FROM organization o 
WHERE o.id IN 
(select 100 + s.id_status FROM database2.status s) 

对于这个，我如何从第二个数据库中的选择中删除这些字段?这很可能是我的疏忽，但我无法查明确切的问题，因为当我用 select * from 替换 delete from 时它起作用...

For this one, how can I delete those fields from that select from 2nd database? It's most likely an oversight on my end but I can't pinpoint the exact problem since it works when I replace delete from with select * from...

推荐答案

是否要从两个表中删除?如果是这样使用JOIN形式和多表删除

Do you want to delete from both tables? If so use JOIN form and multiple-table delete

DELETE t1.*, t2.*
FROM organization t1
JOIN database2.status t2 ON t1.id = t2.id_status  + 100

或

DELETE t1.*, t2.*
FROM organization t1
JOIN database2.status t2 ON t1.id - 100 = t2.id_status

如果 organization 表很小，则首选第二个变体.当然database2.status(id_status)的索引必须存在才能改进.

If organization table is small then 2nd variant is preferred. Of course the index by database2.status (id_status) must exist for improvement.

不，我只想从组织中删除数据，因为状态必须保持不变，因为它用于将数据导入其他表

No, I just want to delete data from organization since status has to remain intact because it is used for importing data into other tables

如果是这样，只需使用 DELETE t1.* 而不是 DELETE t1.*, t2.*.

If so simply use DELETE t1.* instead of DELETE t1.*, t2.*.

这篇关于从另一个数据库中选择删除数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！