通过内连接删除 3 个表

发布于 2024-10-17 19:44:19 字数 412 浏览 1 评论 0原文

我有三个表，其中一个存储用户基本信息，另一个是个人资料信息，最后一个存储用户图片。

当我删除这些用户时，我需要删除这些表中的所有数据。所以我写了一个这样的查询。

DELETE Kullanicilar FROM Kullanicilar 
INNER JOIN ProfilBilgileri ON Kullanicilar.ID = ProfilBilgileri.UyeID 
INNER JOIN UyeResim ON Kullanicilar.ID = UyeResim.UyeID 
WHERE Kullanicilar.ID=@ID

但它只是从“Kullanicilar”表中删除数据。
有什么建议吗？

编辑：我正在使用 MSSQL 2008，但托管公司 2000，所以我需要兼容的代码。

原文

I have three tables one of this storing users basic information, the other one is profile information and the last one is storing user picture.

When i deleting these user i need to delete all of the data in these tables. So i write a query like this.

DELETE Kullanicilar FROM Kullanicilar 
INNER JOIN ProfilBilgileri ON Kullanicilar.ID = ProfilBilgileri.UyeID 
INNER JOIN UyeResim ON Kullanicilar.ID = UyeResim.UyeID 
WHERE Kullanicilar.ID=@ID

But it just deleting the data from "Kullanicilar" table.
Any suggestions?

EDIT : I'm using MSSQL 2008 but hosting firm 2000 so i need compatible code.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

妥活 2024-10-24 19:44:19

您可以使用像 Xavinou sugested 这样的触发器，或者，如果您的表中有外键，您可以使用“级联删除”选项。您的外键将使用以下命令创建：

FOREIGN KEY ([Id]) REFERENCES AnotherTable
ON DELETE CASCADE

You can use a trigger like Xavinou sugested or, if you have foreign keys in your tables, you can go with Delete on Cascade option. Your foreign keys will be created using this:

FOREIGN KEY ([Id]) REFERENCES AnotherTable
ON DELETE CASCADE

回复收藏 0 原文

够钟 2024-10-24 19:44:19

我想到的解决方案是使用事务。

BEGIN TRANSACTION

DELETE FROM Kullanicilar WHERE ID = @ID
DELETE FROM ProfilBilgileri WHERE UyeID = @ID
DELETE FROM UyeResim WHERE UyeID = @ID

COMMIT TRANSACTION

The solution that springs to mind is to use a transaction.

BEGIN TRANSACTION

DELETE FROM Kullanicilar WHERE ID = @ID
DELETE FROM ProfilBilgileri WHERE UyeID = @ID
DELETE FROM UyeResim WHERE UyeID = @ID

COMMIT TRANSACTION

回复收藏 0 原文

扶醉桌前 2024-10-24 19:44:19

declare @pbid int
declare @urid int    
SELECT @pbid = ProfilBilgileri.UyeID, @urid = UyeResim.UyeID FROM Kullanicilar INNER JOIN ProfilBilgileri ON Kullanicilar.ID = ProfilBilgileri.UyeID INNER JOIN UyeResim ON Kullanicilar.ID = UyeResim.UyeID WHERE Kullanicilar.ID=@ID

delete Kullanicilar where ID = @ID
delete ProfilBilgileri where UyeID = @pbid
delete UyeResim where UyeID = @urid

当然，我假设 Kullanicilar 和其他两个表之间存在一一对应关系。如果是一对多，则其他每个表都需要一个临时表，并根据该临时表进行删除。希望这能为您指明正确的方向！

declare @pbid int
declare @urid int    
SELECT @pbid = ProfilBilgileri.UyeID, @urid = UyeResim.UyeID FROM Kullanicilar INNER JOIN ProfilBilgileri ON Kullanicilar.ID = ProfilBilgileri.UyeID INNER JOIN UyeResim ON Kullanicilar.ID = UyeResim.UyeID WHERE Kullanicilar.ID=@ID

delete Kullanicilar where ID = @ID
delete ProfilBilgileri where UyeID = @pbid
delete UyeResim where UyeID = @urid

Of course, I'm assuming that there's a 1-to-1 correspondence between Kullanicilar and the other two tables. If it's 1-to-many, you'll need a temp table for each of the other tables and delete based on that. Hope this points you in the right direction!

回复收藏 0 原文

~没有更多了~