Showing posts with label update. Show all posts
Showing posts with label update. Show all posts

Wednesday, March 28, 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.

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.

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

Monday, February 20, 2012

query no longer working?

UPDATE PUBLIC_Transaction INNER JOIN ShipTo ON PUBLIC_Transaction.ShipToID =
ShipTo.ID SET PUBLIC_Transaction.ReferenceNumber = [dbo_ShipTo].[Name] WHERE
(((Len([name]))>0))
When I try to run this I get the error message
An Error occurred while executing query: Inccorrect syntax near the keyword
‘inner’
We used to have local support that set this up we were able to run it once
when it was set up and was told we could re run it when ever we needed to
update the infor. Also was told they would send me a new query that would
update this info constantly. Never got the email they disappeared along with
$1000.00 in pre paid support.
Original goal was under a specific customer in the purchase history tab to
have the Customer name from the ship to tab appear in the Reference # field
in the purchase history tab
Any help would be appreciatedHi
Did you ever get this query working?
(untested)
UPDATE PUBLIC_PUBLIC_Transaction SET ReferenceNumber=(SELECT
[dbo_ShipTo].[Name]
FROM ShipTo JOIN PUBLIC_Transaction ON PUBLIC_Transaction.ShipToID =
ShipTo.ID WHERE Len([name])>0 )
WHERE EXISTS (SELECT * FROM ShipTo JOIN PUBLIC_Transaction ON
PUBLIC_Transaction.ShipToID =
ShipTo.ID WHERE Len([name])>0)
"shoeman240" <shoeman240@.discussions.microsoft.com> wrote in message
news:CA97C711-EFD2-49A1-9C08-3EDC81DB4CE4@.microsoft.com...
> UPDATE PUBLIC_Transaction INNER JOIN ShipTo ON PUBLIC_Transaction.ShipToID
> =
> ShipTo.ID SET PUBLIC_Transaction.ReferenceNumber = [dbo_ShipTo].[Name]
> WHERE
> (((Len([name]))>0))
>
> When I try to run this I get the error message
> An Error occurred while executing query: Inccorrect syntax near the
> keyword
> inner
> We used to have local support that set this up we were able to run it once
> when it was set up and was told we could re run it when ever we needed to
> update the infor. Also was told they would send me a new query that would
> update this info constantly. Never got the email they disappeared along
> with
> $1000.00 in pre paid support.
> Original goal was under a specific customer in the purchase history tab to
> have the Customer name from the ship to tab appear in the Reference #
> field
> in the purchase history tab
> Any help would be appreciated
>|||Hello,
This query never worked in SQL Server (it probably worked in Microsoft
Access). An equivalent T-SQL query would be:
UPDATE PUBLIC_Transaction
SET ReferenceNumber = ShipTo.Name
FROM PUBLIC_Transaction INNER JOIN ShipTo
ON PUBLIC_Transaction.ShipToID = ShipTo.ID
WHERE Len(name)>0
And another equivalent query (in standard ANSI-SQL) is:
UPDATE PUBLIC_Transaction
SET ReferenceNumber = (
SELECT Name FROM ShipTo
WHERE PUBLIC_Transaction.ShipToID = ShipTo.ID
AND Len(name)>0
) WHERE EXISTS (
SELECT * FROM ShipTo
WHERE PUBLIC_Transaction.ShipToID = ShipTo.ID
AND Len(name)>0
)
Razvan