delete query 1

[swoodring]

I am trying to do an inner join and based on the join delete the records. Here's the query I'm using. Basically I want to delete all detail records that are found in the header where the transactiontype is '00' What am I doing wrong. It gives me the following syntex error - incorrect syntex near d. This query works fine as just a select statment.

delete salesdetailretail d
join salesheaderretail h
on h.storenumber = d.storenumber and
h.controller = d.controller and
h.register# = d.registernum and
h.transaction# = d.transactionnum and
h.transactiondate = d.transactiondate

where transactiontype = '00'
order by d.transactiondate, d.transactionnum, d.sequencenum

 

[foxdev]

The syntax structure shown in BOL doesn't show the capability to provide a table alias ("d&quot. Robert Bradley

http://www.foxdev.com/

Got extra money lying around? Visit:

http://www.agrainofhope.org

 

[Palmer]

Does this work

delete d
from salesdetailretail d
join salesheaderretail h
on h.storenumber = d.storenumber and
h.controller = d.controller and
h.register# = d.registernum and
h.transaction# = d.transactionnum and
h.transactiondate = d.transactiondate
where transactiontype = '00'

 

[swoodring]

that worked. Thanks.