What Is Wrong With This query?

Update garment_details
SET garment_details.blankprice = '1.50'
Where garment_details.GarmentDetailID
in (Select
garment_details.GarmentDetailID
From garment_details
Inner Join colors ON garment_details.ColorID =
colors.ColorID
Where
colors.ColorType = '1' AND
garment_details.GarmentID = 'fol3930r'
)

Re: What Is Wrong With This query?

"kevinjbowman" wrote in message
news:1141928220.525434.105800@p10g2000cwp.googlegroups.com.. .
> Update garment_details
> SET garment_details.blankprice = '1.50'
> Where garment_details.GarmentDetailID
> in (Select
> garment_details.GarmentDetailID
> From garment_details
> Inner Join colors ON garment_details.ColorID =
> colors.ColorID
> Where
> colors.ColorType = '1' AND
> garment_details.GarmentID = 'fol3930r'
> )

It would be helpful when posting question if you would include details about
your environment and the symptoms of any error.
For instance, is this query causing an error? What is the error?
Or does it function correctly, but too slowly?
Or do you just want to know if it's good SQL coding style?

My guess is that you're using MySQL 4.0 or earlier, which doesn't support
subqueries. If so, you're getting a syntax error near where the "(Select"
subquery begins.

If you're using at least 4.0.4, you can try this instead:

UPDATE garment_details g INNER JOIN colors c ON g.ColorID = c.ColorID
SET g.blankprice = '1.50'
WHERE g.GarmentDetailID = 'fol3930r' AND c.ColorType = '1';

This uses multi-table update syntax, which is an extension to SQL that MySQL
offers. It isn't standard SQL, but it's very useful.

Regards,
Bill K.