query problem

The subselect in the query returns 897 rows, but when it
is included in the
where clause of an update statement, the whole table is
returned and
updated. Why? And how can I change this to only update
the 897 rows that
the subselect is returning?
UPDATE Item
SET sz_amount = 1
WHERE item_id IN (SELECT Item.item_id FROM dbo.Item INNER
JOIN dbo.ItemExtended ON dbo.Item.item_id =
dbo.ItemExtended.item_id INNER JOIN dbo.IRISubcategory ON
dbo.IRISubcategory.iri_subcategory_id =
dbo.ItemExtended.iri_subcategory_id INNER JOIN
dbo.IRICategory ON dbo.IRISubcategory.iri_category_id
= dbo.IRICategory.iri_category_id WHERE
(dbo.Item.sz_amount = 10) AND
(dbo.IRICategory.code = '1820'))
I'm not sure but try this an tell me if it works.
UPDATE Item
SET sz_amount = 1
FROM dbo.Item INNER
JOIN dbo.ItemExtended ON dbo.Item.item_id = dbo.ItemExtended.item_id
JOIN dbo.IRISubcategory ON dbo.IRISubcategory.iri_subcategory_id =
dbo.ItemExtended.iri_subcategory_id
JOIN dbo.IRICategory ON dbo.IRISubcategory.iri_category_id =
dbo.IRICategory.iri_category_id
WHERE dbo.Item.sz_amount = 10 AND
dbo.IRICategory.code = '1820'
--Buddy
"Jamie Elliott" <jelliott@.alexlee.com> wrote in message
news:253d01c427cf$38e3e730$a501280a@.phx.gbl...
> The subselect in the query returns 897 rows, but when it
> is included in the
> where clause of an update statement, the whole table is
> returned and
> updated. Why? And how can I change this to only update
> the 897 rows that
> the subselect is returning?
>
> UPDATE Item
> SET sz_amount = 1
> WHERE item_id IN (SELECT Item.item_id FROM dbo.Item INNER
> JOIN dbo.ItemExtended ON dbo.Item.item_id =
> dbo.ItemExtended.item_id INNER JOIN dbo.IRISubcategory ON
> dbo.IRISubcategory.iri_subcategory_id =
> dbo.ItemExtended.iri_subcategory_id INNER JOIN
> dbo.IRICategory ON dbo.IRISubcategory.iri_category_id
> = dbo.IRICategory.iri_category_id WHERE
> (dbo.Item.sz_amount = 10) AND
> (dbo.IRICategory.code = '1820'))
>
>
>
|||Hi Jamie,
From your descriptions, I know your subselect query will work fine and get
the correct result alone. However it goes wrong when you make it as
subselect.
Would you please have a try on Buddy Ackerman's query and tell me whether
it works. If it doesn't, would you please show me your DDL and I could
reproduce it on my machine
Thank you for your patience and cooperation. If you have any questions or
concerns, don't hesitate to let me know. We are here to be of assistance!
Sincerely yours,
Michael Cheng
Microsoft Online Support
************************************************** *********
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks.