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?

Possible duplicate of [How to Delete using INNER JOIN with SQL Server?](http://stackoverflow.com/questions/16481379/how-to-delete-using-inner-join-with-sql-server) — Ralph, Feb 20 '17 at 15:59
I just edited the OP and made some indentations to the `DELETE`query. Like this it should be easy to see the problems with the second `FROM` and the second `WHERE`. Just remove the first `FROM` (as proposed below) and replace the last `WHERE` with an `AND`. Then it should work. BTW: proper formatting is always a great way in SQL to identify possible obstacles / errors. — Ralph, Feb 20 '17 at 16:22
That entry doesn't cover how to alias a select query in a delete statement. — Chris Searcy, Feb 20 '17 at 16:22
@Ralph I removed the extra from, and replaced the last where with an AND, but it still won't execute. It still doesn't like the aliasing. — Chris Searcy, Feb 20 '17 at 16:27

score 2 · Accepted Answer · edited Jul 29 '20 at 18:25

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

I get the same errors. The aliasing it's complaining about are in the select statements where I refer to them as 'old' and 'new' — Chris Searcy, Feb 20 '17 at 15:59
@SqlZim There is not only one `from` too many but also a `where` (at the end) too many. — Ralph, Feb 20 '17 at 16:13
@SqlZim this is very close (much cleaner by the way, thanks!), but now, on both queries I get a different error message: `Msg 4104, Level 16, State 1, Line 8 The multi-part identifier "rd.PartNumberID" could not be bound.` — Chris Searcy, Feb 20 '17 at 16:48
@ChrisSearcy Fixed. I managed to get a join out of order while rearranging everything. — SqlZim, Feb 20 '17 at 16:55

Convert Update statement to Delete statement with aliasing

1 Answers1