server清除快取 - sql刪除一筆資料




如何使用SQL Server刪除INNER JOIN? (10)

這是一個簡單的查詢,一次刪除兩個表中的記錄。

DELETE table1.* ,
       table2.* 
FROM table1 
INNER JOIN table2 ON table1.id= table2.id where table1.id ='given_id'

我想在SQL Server 2008使用INNER JOIN進行刪除。

但我得到這個錯誤,

Msg 156,Level 15,State 1,Line 15
在關鍵字'INNER'附近的語法不正確。

我的代碼:

DELETE FROM WorkRecord2 
INNER JOIN Employee ON EmployeeRun=EmployeeNo
WHERE Company = '1' AND Date = '2013-05-06'

以下是我目前用於刪除甚至更新的內容:

DELETE FROM      w
FROM             WorkRecord2   w,
                 Employee      e
WHERE            w.EmployeeRun = e.EmployeeNo
             AND w.Company = '1' 
             AND w.Date = '2013-05-06'

只需在要從中刪除記錄的位置在DELETEFROM之間添加表名,因為我們必須指定要刪除的表。 同時刪除ORDER BY子句,因為在刪除記錄時沒有任何要訂購的東西。

所以你的最終查詢應該是這樣的:

    DELETE WorkRecord2 
      FROM WorkRecord2 
INNER JOIN Employee 
        ON EmployeeRun=EmployeeNo
     WHERE Company = '1' 
       AND Date = '2013-05-06';

可能這對你有幫助 -

DELETE FROM dbo.WorkRecord2 
WHERE EmployeeRun IN (
    SELECT e.EmployeeNo
    FROM dbo.Employee e
    WHERE ...
)

或者試試這個 -

DELETE FROM dbo.WorkRecord2 
WHERE EXISTS(
    SELECT 1
    FROM dbo.Employee e
    WHERE EmployeeRun = e.EmployeeNo
        AND ....
)

在SQL Server Management Studio中,我可以輕鬆創建一個SELECT查詢。

SELECT Contact.Naam_Contactpersoon, Bedrijf.BedrijfsNaam, Bedrijf.Adres, Bedrijf.Postcode
FROM Contact
INNER JOIN Bedrijf ON Bedrijf.IDBedrijf = Contact.IDbedrijf

我可以執行它,並顯示我的所有聯繫人。

現在將SELECT更改為DELETE:

DELETE Contact
FROM Contact
INNER JOIN Bedrijf ON Bedrijf.IDBedrijf = Contact.IDbedrijf

您在SELECT語句中看到的所有記錄都將被刪除。

你甚至可以用同樣的過程創建一個更難的內連接,例如:

DELETE FROM Contact
INNER JOIN Bedrijf ON Bedrijf.IDBedrijf = Contact.IDbedrijf
INNER JOIN LoginBedrijf ON Bedrijf.IDLoginBedrijf = LoginBedrijf.IDLoginBedrijf

它應該是:

DELETE zpost 
FROM zpost 
INNER JOIN zcomment ON (zpost.zpostid = zcomment.zpostid)
WHERE zcomment.icomment = "first"       

您需要指定要刪除的表格,這裡是一個帶有別名的版本:

DELETE w
FROM WorkRecord2 w
INNER JOIN Employee e
  ON EmployeeRun=EmployeeNo
WHERE Company = '1' AND Date = '2013-05-06'

試試這個查詢:

DELETE WorkRecord2, Employee 
FROM WorkRecord2 
INNER JOIN Employee ON (tbl_name.EmployeeRun=tbl_name.EmployeeNo)
WHERE tbl_name.Company = '1' 
AND tbl_name.Date = '2013-05-06';

這個版本應該可以

DELETE WorkRecord2
FROM WorkRecord2 
INNER JOIN Employee ON EmployeeRun=EmployeeNo
Where Company = '1' AND Date = '2013-05-06'

這是我的SQL Server版本

DECLARE @ProfileId table(Id bigint)

DELETE FROM AspNetUsers
OUTPUT deleted.ProfileId INTO @ProfileId
WHERE Email = @email

DELETE FROM UserProfiles    
WHERE Id = (Select Id FROM @ProfileId)




sql-delete