Delete SQL query errors

Question

I receive an error, from the below statement; It runs perfectly as a select statement, but fails as a delete. Any idea's how to get this statement working as a delete? Seems to error out at C.*

DELETE C.*
FROM
  [CRM_VNB].[dbo].[CATALOGUE] AS C
  LEFT JOIN VNODAT.dbo.ARCUS AS A ON A.IDCUST = C.IDCUST
WHERE
  A.IDCUST IS NULL

[How to Delete using INNER JOIN with SQL Server?](https://stackoverflow.com/questions/16481379/how-to-delete-using-inner-join-with-sql-server) — Abra, May 07 '20 at 17:01
Please in code questions give a [mre]--cut & paste & runnable code, including smallest representative example input as code; desired & actual output (including verbatim error messages); tags & versions; clear specification & explanation. Give the least code you can give that is code that you show is OK extended by code that you show is not OK. (Debugging fundamental.) For SQL that includes DBMS & DDL (including constraints & indexes) & input as code formatted as a table. [ask] Stop trying to code your overall goal & explain what you expected instead from the given code & why. — philipxy, May 07 '20 at 18:11
This is a faq. Before considering posting please read your textbook and/or manual & google any error message or many clear, concise & precise phrasings of your question/problem/goal, with & without your particular strings/names & site:stackoverflow.com & tags; read many answers. If you post a question, use one phrasing as title. Reflect your research. See [ask] & the voting arrow mouseover texts. — philipxy, May 07 '20 at 18:12
Does this answer your question? [How to Delete using INNER JOIN with SQL Server?](https://stackoverflow.com/questions/16481379/how-to-delete-using-inner-join-with-sql-server) — philipxy, May 07 '20 at 18:12

score 1 · Accepted Answer · answered May 07 '20 at 16:53

This looks like SQL Server syntax (because of the square brackets surrounding identifiers).

In that case, the problem is with c.*; you should be using the "raw" table alias instead:

delete c
from crm_vnb.dbo.catalogue as c 
left join vnodat.dbo.arcus a on a.idcust = c.idcust
where a.custid is null

Note that you could also phrase this with not exists:

delete c
from crm_vnb.dbo.catalogue as c 
where not exists (select 1 from vnodat.dbo.arcus as a where a.idcust = c.idcust)

score 0 · Answer 2 · answered May 07 '20 at 16:55

0

Do not use c.*

simply

DELETE  FROM [CRM_VNB].[dbo].[CATALOGUE] AS C LEFT JOIN VNODAT.dbo.ARCUS AS A ON A.IDCUST = C.IDCUST WHERE A.IDCUST IS NULL

answered May 07 '20 at 16:55

Tranquillity

237
2
9

Delete SQL query errors

2 Answers2