CODEWITHSUNDEEP
sql-server
Ben Durkin
Ben Durkin

Reputation: 447

SQL Server : correct DELETE syntax

I have this:

SELECT * 
FROM tblPricing pricing 
LEFT OUTER JOIN PUB_CAR.dbo.CAPVehicles vehicle ON pricing.capid = vehicle.CVehicle_ID
WHERE 
    pricing.[type] = 'car' 
    AND pricing.source = 'ARVAL' 
    AND vehicle.cvehicle_mantext = 'BMW' 
    AND vehicle.cvehicle_modtext = '5 SERIES DIESEL SALOON'  
    AND CVehicle_ManText IS NOT NULL

That works fine and I get the correct results.

But, how do I delete that same result set. I tried

DELETE FROM tblPricing pricing 
LEFT OUTER JOIN PUB_CAR.dbo.CAPVehicles vehicle ON pricing.capid = vehicle.CVehicle_ID 
WHERE 
     pricing.[type] = 'car' 
     AND pricing.source = 'ARVAL' 
     AND vehicle.cvehicle_mantext = 'BMW' 
     AND vehicle.cvehicle_modtext = '5 SERIES DIESEL SALOON'  
     AND CVehicle_ManText IS NOT NULL

But that says, incorrect syntax near 'pricing'

Thanks

Upvotes: 2

Views: 59

Answers (4)

Damien_The_Unbeliever
Damien_The_Unbeliever

Reputation: 239704

A DELETE statement can optionally have two FROM clauses. The first is used to identify the table to delete from. The second is used to introduce a FROM clause that looks the same as that in a SELECT statement. Despite them using the same keyword, they are not the same.

If you just have DELETE FROM xxx then it's the first variant, and cannot be used to start introducing JOIN clauses. So the complete form you want is:

DELETE FROM tblPricing --<-- FROM
FROM tblPricing pricing --<-- FROM again. Different feature
LEFT OUTER JOIN PUB_CAR.dbo.CAPVehicles vehicle 
ON pricing.capid = vehicle.CVehicle_ID 
WHERE pricing.[type] = 'car' 
AND pricing.source = 'ARVAL' AND vehicle.cvehicle_mantext = 'BMW'
AND vehicle.cvehicle_modtext = '5 SERIES DIESEL SALOON'
AND CVehicle_ManText IS NOT NULL

The other answers have removed the first FROM keyword because it is optional (but still, the second type of FROM clause has to come after the table to delete from has been identified)

Upvotes: 0

mohan111
mohan111

Reputation: 8865

DELETE P FROM tblPricing  
LEFT OUTER JOIN PUB_CAR.dbo.CAPVehicles vehicle 
ON P.capid = vehicle.CVehicle_ID 
WHERE P.[type] = 'car' 
AND P.source = 'ARVAL' 
AND vehicle.cvehicle_mantext = 'BMW'
 AND vehicle.cvehicle_modtext

Upvotes: 0

Fka
Fka

Reputation: 6234

You are missing table alias after DELETE keyword. 

DELETE pricing FROM tblPricing AS pricing 
LEFT OUTER JOIN PUB_CAR.dbo.CAPVehicles vehicle ON pricing.capid = vehicle.CVehicle_ID 
WHERE pricing.[type] = 'car' AND 
      pricing.source = 'ARVAL' AND 
      vehicle.cvehicle_mantext = 'BMW' AND 
      vehicle.cvehicle_modtext = '5 SERIES DIESEL SALOON' AND 
      CVehicle_ManText IS NOT NULL

Upvotes: 1

Tim Schmelter
Tim Schmelter

Reputation: 460148

You have to specify the table alias first. So instead of this:

DELETE FROM tblPricing pricing ....

this:

DELETE pricing 
FROM   tblPricing pricing 
LEFT OUTER JOIN PUB_CAR.dbo.CAPVehicles vehicle 
ON              pricing.capid = vehicle.CVehicle_ID 
WHERE           pricing.[type] = 'car' 
AND             pricing.source = 'ARVAL' 
AND             vehicle.cvehicle_mantext = 'BMW' 
AND             vehicle.cvehicle_modtext = '5 SERIES DIESEL SALOON' 
AND             CVehicle_ManText IS NOT NULL

Upvotes: 1

Related Questions