Syntax problem DELETE with Subquery

Hello,

I have the following SELECT Statement working:
SELECT * FROM NDTab AS C
 WHERE StatusTab = '1'
 AND StatusView = 'A'
 AND StatusExport = 'N'
 AND NDTicket LIKE '%FNB%'
 AND EXISTS (SELECT * FROM PSTab AS O
       WHERE O.NDTicket = C.NDTicket
       AND LData LIKE '%;Started      ;%')

Now I want to do similar with the DELETE Statement:
DELETE FROM NDTab AS C
 WHERE StatusTab = '1'
 AND StatusView = 'A'
 AND StatusExport = 'N'
 AND NDTicket LIKE '%FNB%'
 AND EXISTS (SELECT * FROM PSTab AS O
       WHERE O.NDTicket = C.NDTicket
       AND LData LIKE '%;Started      ;%')

But I get the error: SQL logic error or missing database near "AS": syntax error

Can someone help me please how to make the DELETE Statement working?


Regards
CSN22

May 29th, 2015 2:50pm

Try this (though I haven't tested)

DELETE c
FROM NDTab  C 
 WHERE StatusTab = '1' 
 AND StatusView = 'A' 
 AND StatusExport = 'N' 
 AND NDTicket LIKE '%FNB%' 
 AND EXISTS (SELECT * FROM PSTab AS O 
       WHERE O.NDTicket = C.NDTicket 
       AND LData LIKE '%;Started      ;%')

Free Windows Admin Tool Kit Click here and download it now
May 29th, 2015 2:54pm

Use below query

DELETE FROM C
FROM dbo.NDtab AS C
JOIN PSTab AS O ON O.NDTicket = C.NDTicket AND LData LIKE '%;Started      ;%'
WHERE StatusTab = '1' 
 AND StatusView = 'A' 
 AND StatusExport = 'N' 
 AND NDTicket LIKE '%FNB%' 


Hope this will help

May 29th, 2015 3:10pm

You can use the the double FROM syntax or a CTE. Here is an example:

create table atable (id int,col1 int)
insert into atable values(1,1),(2,10),(3,30)
create table btable (id int,col1 int)
insert into btable values (3,30)



select * from atable 

--Option 1
DELETE FROM  a
FROM atable a
WHERE EXISTS (Select 1 From btable b WHERE a.id=b.id)

--Option 2
--;with mycte as (
--Select *
--from atable a
-- WHERE EXISTS (Select 1 From btable b WHERE a.id=b.id)
--)
--delete from mycte

select * from atable 



drop table atable, btable 

Free Windows Admin Tool Kit Click here and download it now
May 29th, 2015 3:11pm

No you can't.  That won't compile since the alias C is not defined.
May 29th, 2015 3:13pm

Thanks for the answer, but I just found out the Syntax:
DELETE FROM NDTab
 WHERE StatusTab = '1'
 AND StatusView = 'A'
 AND StatusExport = 'N'
 AND NDTicket LIKE '%FNB%'
 AND EXISTS (SELECT * FROM PSTab
       WHERE PSTab.NDTicket = NDTab.NDTicket
       AND PSTab.LData LIKE '%;Started      ;%')

This is working. But now I run into another problem. My first DELETE is:
DELETE FROM PSTab
 WHERE EXISTS (SELECT * FROM NDTab
        WHERE PSTab.StatusTab = '1'
        AND PSTab.NF = NDTab.NF
        AND PSTab.LData LIKE '%;Started      ;%'
        AND NDTab.StatusView = 'A'
        AND NDTab.StatusExport = 'N')

And the 2nd DELETE would be:
DELETE FROM NDTab
 WHERE StatusTab = '1'
 AND StatusView = 'A'
 AND StatusExport = 'N'
 AND NDTicket LIKE '%FNB%'
 AND EXISTS (SELECT * FROM PSTab
       WHERE PSTab.NDTicket = NDTab.NDTicket
       AND PSTab.LData LIKE '%;Started      ;%')

I run into the problem, that the 2nd DELETE Statement is based on rows that are already deleted with DELETE statement 1.

Is there any chance to solve this with SQL statemens? Can one delete rows with one DELETE Statement from two tables?


Regards
CSN22

Free Windows Admin Tool Kit Click here and download it now
May 29th, 2015 3:19pm

You can use output clause for the same.. use output for first delete statement

DELETE FROM PSTab 
OUTPUT DELETED.*
  INTO #DeletedRecords
 WHERE EXISTS (SELECT * FROM NDTab 
        WHERE PSTab.StatusTab = '1' 
        AND PSTab.NF = NDTab.NF 
        AND PSTab.LData LIKE '%;Started      ;%' 
        AND NDTab.StatusView = 'A' 
        AND NDTab.StatusExport = 'N')

SELECT * FROM #deletedrecords

Remember to drop the temp table in the end. 

May 29th, 2015 3:24pm

Now I get this error:
SQL logic error or missing database near "OUTPUT": syntax error

I also tried to create a table first instead of using temp table, but it gives the same error. What is wrong?

I tried your syntax and also:
DELETE FROM PSTab
OUTPUT DELETED
.*
 
INTO PSTabDelete
  WHERE EXISTS (SELECT * FROM NDTab
       
WHERE PSTab.StatusTab = '1'
       
AND PSTab.NF = NDTab.NF
       
AND PSTab.LData LIKE '%;Started      ;%'
       
AND NDTab.StatusView = 'A'
       
AND NDTab.StatusExport = 'N')

And I had PSTabDelete successfully created before I ran the DELETE statement

Both give the same

Free Windows Admin Tool Kit Click here and download it now
May 29th, 2015 3:44pm

Read Steve Kass blog about DELETE FROM tricks :-)

http://sqlblog.com/blogs/steve_kass/archive/2009/04/30/delete-from-where.aspx

May 31st, 2015 2:56am

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics