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.
Showing posts with label subselect. Show all posts
Showing posts with label subselect. Show all posts
Wednesday, March 28, 2012
Monday, March 26, 2012
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.
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.
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.sql
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.sql
Tuesday, March 20, 2012
QUERY performance
when I issue a join query between T1(87 rows) and a
subselect(x10^6 rows)
the optimizer estimation row count is only 1
the query has to scan the primary clustered indexes in
all the tables that partcipate in the subselect (no other
way).
I already rum update statistics on all the objects
involved without success
that causes the server to use T1 as the outher table in a
nested loop join which makes the query run for 10 hours
instead of 10 minutes
Any ideas?Perhaps you could try updating the statistics with the FULLSCAN option?
Peter Yeoh
http://www.yohz.com
Need smaller backups? Try MiniSQLBackup
"Amit" <amit.eshet@.intel.com> wrote in message
news:190f101c44c8e$c9932ff0$a401280a@.phx.gbl...
> when I issue a join query between T1(87 rows) and a
> subselect(x10^6 rows)
> the optimizer estimation row count is only 1
> the query has to scan the primary clustered indexes in
> all the tables that partcipate in the subselect (no other
> way).
> I already rum update statistics on all the objects
> involved without success
> that causes the server to use T1 as the outher table in a
> nested loop join which makes the query run for 10 hours
> instead of 10 minutes
> Any ideas?
subselect(x10^6 rows)
the optimizer estimation row count is only 1
the query has to scan the primary clustered indexes in
all the tables that partcipate in the subselect (no other
way).
I already rum update statistics on all the objects
involved without success
that causes the server to use T1 as the outher table in a
nested loop join which makes the query run for 10 hours
instead of 10 minutes
Any ideas?Perhaps you could try updating the statistics with the FULLSCAN option?
Peter Yeoh
http://www.yohz.com
Need smaller backups? Try MiniSQLBackup
"Amit" <amit.eshet@.intel.com> wrote in message
news:190f101c44c8e$c9932ff0$a401280a@.phx.gbl...
> when I issue a join query between T1(87 rows) and a
> subselect(x10^6 rows)
> the optimizer estimation row count is only 1
> the query has to scan the primary clustered indexes in
> all the tables that partcipate in the subselect (no other
> way).
> I already rum update statistics on all the objects
> involved without success
> that causes the server to use T1 as the outher table in a
> nested loop join which makes the query run for 10 hours
> instead of 10 minutes
> Any ideas?
Subscribe to:
Posts (Atom)