0

Hello I am working on a very simple query, this query is suppose to delete all the null rows on my table, I have it this is my query

DELETE FROM myTable WHERE myID IS NULL;

But I wanted to include this query on a store procedure, this time I want to remove all the null values from two tables in just one query this is my progress: 

DELETE tblOg.myID, TblOrd.myID
FROM myTbl tblOg,
      myTblOrdered TblOrd
WHERE myTbl.myID IS NULL AND myTblOrdered.myID IS NULL;

But I got this error

Msg 102, Level 15, State 1, Line 7 Incorrect syntax near 'TblOrd'.

Not sure if I should use a INNER JOIN or where should I put it.

Thanks in advance.

Fernando López
  • 1
  • 1
  • what RDBMS are you using? – Lamak Nov 07 '17 at 14:44
  • 8
    You can only delete from one table with the `DELETE` statement. –  Nov 07 '17 at 14:45
  • @Lamak Microsoft SQL Server – Fernando López Nov 07 '17 at 14:48
  • The FROM keyword in DELETE statements is often used in a confusing way. We can say `DELETE myTable WHERE myID IS NULL` (*without FROM*) which does exactly the same as your first query. You can then add FROM before the WHERE to specify additional table joins, to help identify rows to delete from the first table. I find it a stupid language problem that FROM is allowed when it is not needed - IMO it should only have been allowed in DELETE to specify JOINs, but that isn't the case. – Peter B Nov 07 '17 at 15:13

1 Answers1

0

Can't you just separate them by a semicolon like this :

DELETE FROM table1 WHERE myID IS NULL;
DELETE FROM table2 WHERE myID IS NULL;

OR

use inner join

DELETE FROM table1 INNER JOIN table2 WHERE table1.myID IS NULL AND table2.myID IS NULL.
brahimfes
  • 112
  • 7
  • The second part of your answer will not work. You are still only deleting from 1 table and secondly you cannot join null values. – SQLChao Nov 07 '17 at 15:14