Deleting With join on code igniter MSSQL return an error

Question

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:

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

You can't able to delete 2 table from single delete query in MSSQL. — Mano, Feb 07 '19 at 05:29
Possible duplicate of [How to Delete using INNER JOIN with SQL Server?](https://stackoverflow.com/questions/16481379/how-to-delete-using-inner-join-with-sql-server) — Suraj Kumar, Feb 07 '19 at 05:46
@SurajKumar see update i linked the same exact link its not working — guradio, Feb 07 '19 at 05:50
@guradio You are not following the idea suggested there as I said for join you need to give alias name. Can you post sample data at least 2-4 rows and expected output after delete? — Suraj Kumar, Feb 07 '19 at 05:52
@guradio You have following MySql post and tagged as Sql server (MSSQL) both are different. Please clarify which is correct? — Suraj Kumar, Feb 07 '19 at 05:55
@SurajKumar it said `You need to specify what table you are deleting from, here is a version with an alias: ` it didnt say you need alias. — guradio, Feb 07 '19 at 05:56

PSK · Accepted Answer · 2019-02-07T05:42:14.850

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.

Suraj Kumar · Answer 2 · 2019-02-07T06:19:00.533

You need to set alias for the table for which you want to delete as shown in below example as w is used for WorkRecord2. It is required when needed with inner join delete or update statements.

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

Also only one table delete is allowed. Deleting with more than one table is allowed in MySQL not MSSQL. You can find the live demo Here.

Below is the query with sample data.

create table WorkRecord2 (EmployeeRun int, empWorkNo varchar(10))
create table Employee (EmployeeNo int, empWorkNo varchar(10), company int, createdDate datetime)

insert into WorkRecord2 Values (1, 'EMW101'),(2, 'EMW102'),(3, 'EMW103'),(4, 'EMW104'),(5, 'EMW105')
insert into Employee values(1, 'EMW101', 1, GETDATE()),(2, 'EMW103', 2, GETDATE())

Select * from WorkRecord2
Select * from Employee

-- This will delete with employeeRun 1 and empWorkNo EMW101
DELETE w
FROM WorkRecord2 w
INNER JOIN Employee e
  ON EmployeeRun=EmployeeNo
WHERE Company = 1 and Cast(createdDate as DATE) = Cast(GETDATE() as DATE)

Select * from WorkRecord2
Select * from Employee

Before delete the table data is as shown below.

EmployeeRun empWorkNo
---------------------
1           EMW101
2           EMW102
3           EMW103
4           EMW104
5           EMW105

After delete with inner join the table data is as shown blow.

EmployeeRun empWorkNo
---------------------
2           EMW102
3           EMW103
4           EMW104
5           EMW105

@guradio I have added explanation with sample data to show it is correct answer. You where condition need to check if data is not deleting. Also check whether record is coming in inner join or not because only that record will be deleted which will match in inner join. — Suraj Kumar, Feb 07 '19 at 06:20
this does not satisfy my OP it will delete one table only how is it the correct answer? — guradio, Feb 07 '19 at 06:49
@guradio Deleting with more than one table is allowed in MySQL not MSSQL. — Suraj Kumar, Feb 07 '19 at 06:50

Deleting With join on code igniter MSSQL return an error

2 Answers2