Remove rows where column exists in select statement

Question

I want to delete rows who specific column exists in select statement like:

delete from [ProjectCustomer] pcc 
where ProjectKey in (
  select p.ProjectKey  
  FROM [Project] AS [p] inner JOIN [ProjectDesign] AS [pd] ON [p].[ProjectKey] = [pd].[ProjectKey]
  inner JOIN [Design] AS [d] ON [pd].[DesignKey] = [d].[DesignKey]
  inner JOIN [ProjectCustomer] AS [pc] ON [pc].[ProjectKey] = [p].[ProjectKey]
  inner join Customer as c on pc.CustomerKey = c.CustomerKey
  where d.Folio = 3014)

But in where clause of delete statement I get

Incorrect syntax near where clause

What am I doing wrong? Regards

Answer 1

DELETE statement with table alias has different syntax, so either you can remove the alias as it's unused or change the delete statement like @Martin commented.

delete pcc from [ProjectCustomer] pcc

I would preper to revise the query using INNER JOIN instead of IN

delete pcc from [ProjectCustomer] AS pcc 
  inner join [Project] AS [p] ON [p].ProjectKey = [pcc].ProjectKey
  inner JOIN [ProjectDesign] AS [pd] ON [p].[ProjectKey] = [pd].[ProjectKey]
  inner JOIN [Design] AS [d] ON [pd].[DesignKey] = [d].[DesignKey]
  inner JOIN [ProjectCustomer] AS [pc] ON [pc].[ProjectKey] = [p].[ProjectKey]
  inner join Customer as c on pc.CustomerKey = c.CustomerKey
  where d.Folio = 3014)

Answer 2

I think the error message is

Incorrect syntax near 'pcc'

instead of

Incorrect syntax near where clause

You wil get the first error message cause you are using the alias wrong, so while you are not joining the target table, you may want

delete from [ProjectCustomer]
where ProjectKey in (
  select p.ProjectKey  
  FROM [Project] AS [p] inner JOIN [ProjectDesign] AS [pd] ON [p].[ProjectKey] = [pd].[ProjectKey]
  inner JOIN [Design] AS [d] ON [pd].[DesignKey] = [d].[DesignKey]
  inner JOIN [ProjectCustomer] AS [pc] ON [pc].[ProjectKey] = [p].[ProjectKey]
  inner join Customer as c on pc.CustomerKey = c.CustomerKey
  where d.Folio = 3014);

or even if you still want to use an alias

delete pcc from [ProjectCustomer] pcc
where pcc.ProjectKey in ...

Answer 3

You can probably simplify your query a little. I think that you don't need a subquery, you can specify the table to delete from in the first FROM clause (without an alias) and join the tables in a second (using aliases as you like):

DELETE FROM ProjectCustomer
FROM ProjectCustomer pc
  INNER JOIN ProjectDesign pd ON pc.ProjectKey = pd.ProjectKey
    INNER JOIN Design d ON pd.DesignKey = d.DesignKey
WHERE d.Folio = 3014;

The Customers and Products table should not be neccessary, as long as you have set up referential integrity and pc.CustomerKey isn't nullable.