Convert Update statement to Delete statement with aliasing

Question

I'm trying to convert a sql server update statement into a delete statement, but when creating the delete statement I get an error regarding aliasing. My original update joins two queries, compares two columns' values and updates where true. My delete will be similar, but will instead delete any rows where either of the above column comparisons are false. Here's my update statement:

UPDATE drdTable
SET DeratingPartNumberID = new.DeratingPartNumberID 
    FROM [ReliabilityData].[dbo].[DeratingRefDes] drdTable, ((SELECT drd.[DeratingPartNumberID], [DeratingPartNumber].[Parameter], [DeratingPartNumber].[Units], drd.[DeratingRefDesID]
        FROM [ReliabilityData].[dbo].[DeratingRefDes] drd
            INNER JOIN [ReliabilityData].[dbo].[ReferenceDesignator] 
                ON [ReferenceDesignator].[ReferenceDesignatorID] = drd.[ReferenceDesignatorID] 
            INNER JOIN [ReliabilityData].[dbo].[DeratingPartNumber] 
                ON [DeratingPartNumber].[DeratingPartNumberID] = drd.[DeratingPartNumberID] 
            INNER JOIN [ReliabilityData].[dbo].[PartNumber] 
                ON [PartNumber].[PartNumberID] = [ReferenceDesignator].[PartNumberID] 
            INNER JOIN [ReliabilityData].[dbo].[BoardRevision] 
                ON [BoardRevision].[BoardRevisionID] = [ReferenceDesignator].[BoardRevisionID] 
        WHERE [PartNumber] = '2000465-203' AND [ReferenceDesignator].[BoardRevisionID] = 335 AND [ReferenceDesignator] IN ('C1','C2','C3','C4')) AS old
    JOIN (SELECT [DeratingPartNumberID], [Parameter], [Units]  
        FROM [ReliabilityData].[dbo].[DeratingPartNumber]
        WHERE [PartNumberID] = 82) AS new ON old.[Parameter] = new.[Parameter] AND old.[Units] = new.[Units])
WHERE drdTable.DeratingRefDesId = old.DeratingRefDesIDenter code here

And here is what I have for my delete statement:

DELETE 
FROM drdTable
FROM [ReliabilityData].[dbo].[DeratingRefDes] AS drdTable 
WHERE DeratingPartNumberID IN (
        (
        SELECT drd.[DeratingPartNumberID], 
               [DeratingPartNumber].[Parameter], 
               [DeratingPartNumber].[Units], 
               drd.[DeratingRefDesID]
        FROM [ReliabilityData].[dbo].[DeratingRefDes] drd
            INNER JOIN [ReliabilityData].[dbo].[ReferenceDesignator] 
                ON [ReferenceDesignator].[ReferenceDesignatorID] = drd.[ReferenceDesignatorID] 
            INNER JOIN [ReliabilityData].[dbo].[DeratingPartNumber] 
                ON [DeratingPartNumber].[DeratingPartNumberID] = drd.[DeratingPartNumberID] 
            INNER JOIN [ReliabilityData].[dbo].[PartNumber] 
                ON [PartNumber].[PartNumberID] = [ReferenceDesignator].[PartNumberID] 
            INNER JOIN [ReliabilityData].[dbo].[BoardRevision] 
                ON [BoardRevision].[BoardRevisionID] = [ReferenceDesignator].[BoardRevisionID] 
        WHERE [PartNumber] = '2000465-203' 
            AND [ReferenceDesignator].[BoardRevisionID] = 335 
            AND [ReferenceDesignator] IN ('C1','C2','C3','C4')
         ) AS old
        JOIN (
            SELECT [DeratingPartNumberID], 
                   [Parameter], 
                   [Units]  
            FROM [ReliabilityData].[dbo].[DeratingPartNumber]
            WHERE [PartNumberID] = 82
              ) AS new 
        ON old.[Parameter] <> new.[Parameter] 
            or old.[Units] <> new.[Units]
        )
WHERE drdTable.DeratingRefDesId = old.DeratingRefDesID

I get error messages complaining about the aliasing I use in my select statements.

Msg 156, Level 15, State 1, Line 13
Incorrect syntax near the keyword 'AS'.
Msg 156, Level 15, State 1, Line 16
Incorrect syntax near the keyword 'AS'.

Note that the aliasing it is referring to is the aliasing of the select queries (i.e. the 'old' and 'new' references. Any suggestions on how I can rewrite my query?

Answer 1

You have an extra from.

When you are deleting from an aliased table, the syntax is delete alias from tbl as alias ...

Do not use old style joins. Bad habits to kick : using old-style JOINs

As far as I can tell, this is a cleaner version of your queries:

for the update:

use ReliabilityData;
go

/* update old with new Id where new Parameter and Units are the same */

update drd
set DeratingPartNumberID = dpn_new.DeratingPartNumberID
from dbo.DeratingRefDes as drd
  inner join dbo.DeratingPartNumber as dpn_old /* old */
    on dpn_old.DeratingPartNumberID = drd.DeratingPartNumberID
  inner join dbo.ReferenceDesignator as rd
    on rd.ReferenceDesignatorID = drd.ReferenceDesignatorID
  inner join dbo.PartNumber as pn
    on pn.PartNumberID = rd.PartNumberID
  -- BoardRevision is not referenced in the where clause, so not needed */
  /* inner join dbo.BoardRevision as br
    on br.BoardRevisionID = rd.BoardRevisionID */
  /* new */
  inner join dbo.DeratingPartNumber as dpn_new 
    on dpn_old.Parameter = dpn_new.Parameter 
   and dpn_old.Units     = dpn_new.Units
where pn.PartNumber = '2000465-203'
  and rd.BoardRevisionID = 335 
  and rd.ReferenceDesignator in ('C1', 'C2', 'C3', 'C4')
  and dpn_new.PartNumberID = 82

and for the delete:

use ReliabilityData;
go

/* delete old where new Parameter and Units are different */

delete drd
from dbo.DeratingRefDes as drd
  inner join dbo.DeratingPartNumber as dpn_old /* old */
    on dpn_old.DeratingPartNumberID = drd.DeratingPartNumberID
  inner join dbo.ReferenceDesignator as rd
    on rd.ReferenceDesignatorID = drd.ReferenceDesignatorID
  inner join dbo.PartNumber as pn
    on pn.PartNumberID = rd.PartNumberID
  -- BoardRevision is not referenced in the where clause, so not needed */
  /* inner join dbo.BoardRevision as br
    on br.BoardRevisionID = rd.BoardRevisionID */
  /* new */
  inner join dbo.DeratingPartNumber as dpn_new 
    on dpn_old.Parameter != dpn_new.Parameter 
   and dpn_old.Units     != dpn_new.Units
where pn.PartNumber = '2000465-203'
  and rd.BoardRevisionID = 335 
  and rd.ReferenceDesignator in ('C1', 'C2', 'C3', 'C4')
  and dpn_new.PartNumberID = 82