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

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      ;%')

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

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 

No you can't.  That won't compile since the alias C is not defined.

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

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. 

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

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 

• Proposed as answer by Olaf HelperMVP 20 minutes ago

Read Steve Kass blog about DELETE FROM tricks :-)

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

Good day CSN22,

Here you have short tutorial regarding OUTPUT:

http://www.tech-recipes.com/rx/47032/a-beginners-guide-to-the-output-clause-in-sql-server/

* Since you did not post your original table structure, and since our ability to read minds is poor, we can not write for you the final query :-(

* I highly recommend and ask you ALWAYS to post your DDL+DML. that mean to post queries to create the tables that relevant to the question and queries to insert some sample data! without this information we need to work more , we need to guess your table structure, and we cant test anything on your structure. we need this information to reproduce your issue! Next time please post DDL+DML :-)