DELETE statment

[DJKAOS]

I'm using MySQL so I can't use subquerys anyway

How can I do this.
I have 3 tables
Parts, Shipments, Suppliers

I need to delete the shipment records that shipped green parts. the part color is located int he Parts table.

I think this will work in Microcsoft SQL but I dont know to do it in MySQL.

Delete from Shipments
where pno in(select pno from Parts where color = 'green');


is there any way to say like...
Delete Shipments from Shipments, Parts
where Parts.color = 'green'

I'm pretty sure that wont work but there must be some way to do it?

Thanks

 

[balves]

Don't know if this will work in MySQL. Probably check the MySQL forum or try it:

DELETE Shipments
FROM Shipments A, Parts B
WHERE A.Pno = B.Pno
AND B.Color = 'Green'

 

[tlbroadbent]

There is a MySQL forum (forum436). That would be the best place to ask your question.

You could modify your example slightly and it may work depending on your version of MySQL.

Delete Shipments
From Shipments, Parts
Where Shipments.pno=Parts.pno
And Parts.color = 'green'
Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.