Can;t delete if inner join

[TRACEYMARYLAND]

hi my select works but when change select to delete from
it errors on inner join


SELECT * FROM DATAWAREHOUSETEST.DBO.SHELTERPRODUCTS
INNER JOIN VMFG.DBO.WORK_ORDER ON DATAWAREHOUSETEST.DBO.SHELTERPRODUCTS.WORKORDER_BASE_ID = VMFG.DBO.WORK_ORDER.BASE_ID
WHERE VMFG.DBO.WORK_ORDER.STATUS IN ('F', 'R')

To

DELETE FROM DATAWAREHOUSETEST.DBO.SHELTERPRODUCTS
INNER JOIN VMFG.DBO.WORK_ORDER ON DATAWAREHOUSETEST.DBO.SHELTERPRODUCTS.WORKORDER_BASE_ID = VMFG.DBO.WORK_ORDER.BASE_ID
WHERE VMFG.DBO.WORK_ORDER.STATUS IN ('F', 'R')

Any one point me in the right direction

 

[donutman]

You need to explicity state which table you want to delete rows from.

[Blue]DELETE[/Blue] Table1
   [Blue]FROM[/Blue] Table1 [Blue]INNER[/Blue] [Gray]JOIN[/Gray] Table2
      [Blue]ON[/Blue] Table1.ID[Gray]=[/Gray]Table2ID

-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[TRACEYMARYLAND]

Like so

DELETE DATAWAREHOUSETEST.DBO.SHELTERPRODUCTS
FROM DATAWAREHOUSETEST.DBO.SHELTERPRODUCTS INNER JOIN VMFG.DBO.WORK_ORDER
ON DATAWAREHOUSETEST.DBO.SHELTERPRODUCTS.WORKORDER_BASE_ID=VMFG.DBO.WORK_ORDER.BASE_ID
WHERE VMFG.DBO.WORK_ORDER.STATUS NOT IN ('F','R')

So before i run it the data will only be deleted from SHELTER PRODUCTS is that true

Cheers

 

[SQLSister]

yes

Questions about posting. See faq183-874
Click here to learn Ways to help with Tsunami Relief

 

[TRACEYMARYLAND]

Thanks i get a bit worried with deletes .....
Cheers

 

[donutman]

As you should...I still do too.
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]