Search code examples
sql-servercodeigniterinner-joinsql-delete

Deleting With join on code igniter MSSQL return an error

To delete with join we use this format

DELETE t1,t2 FROM t1
        INNER JOIN
    t2 ON t2.ref = t1.id 
WHERE
    t1.id = 1;

from www.mysqltutorial.org/mysql-delete-join

Follow the exact way to delete but when I implement in my own version I get error in the comma between two tables to be deleted.

This is what I have:

image

Seen in the above image my implementation and the error I get.

Any idea is appreciated

Update

Same format stackoverflow.com/questions/16481379/how-to-delete-using-inner-join-with-sql-server

Solution

  • SQL Server allows to DELETE only from one table in a DELETE statement.

    In this case you are trying to delete records from two table in a single DELETE statement, which is not allowed.

    For your case you can simply write the same query like following.

    DELETE FROM lngFileId 
WHERE lngId=5

DELETE FROM tbl_FileOrPath
WHERE lngFileId=5

    For More complex scenarios, you can try like following.

    DECLARE @TABLE TABLE 
  ( 
     id INT 
  ) 

INSERT INTO @TABLE 
SELECT t1.ID 
FROM   t1       
WHERE  <YOUR_CONDITION>

DELETE T 
FROM   t1 T 
WHERE  EXISTS (SELECT 1 
               FROM   @TABLE T2 
               WHERE  T2.id = T.id) 

DELETE T 
FROM   t2 T 
WHERE  EXISTS (SELECT 1 
               FROM   @TABLE T2 
               WHERE  T2.id = T.ref)

    Another scenario can be when you have a Foreign Key Relationship between the table with ON DELETE CASCADE enabled. For such cases you need to delete only from the parent table, child table records will automatically gets deleted.