Search code examples
sqlsql-servert-sqljoinsql-delete

Delete SQL query errors

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
Solution

  • 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)