Cannot use subquery in UPDATE statement?

Hi there,
I try to run the following update statement on MS SQL Server 2000 SP4
UPDATE Site
SET Active = 0
WHERE
RID IN (SELECT site.RID AS site_rid
FROM Site INNER JOIN
ManagerSite ms ON site.RID = ms.ArtistSiteID INNER JOIN
Manager m ON ms.ManagerID = m.RID
WHERE (am.UserName = 'iwk1004') AND
(ms.isPrimaryUser = 1))
Running this query I get the message "Subquery returned more than 1
value. This is not permitted when the subquery follows =, !=, <, <= ,
>, >= or when the subquery is used as an expression."
Which is weird, because according to the documentation and examples I
inferred could use UPDATE with the use of IN in the condition and the
subquery returining multiple rows. (sub-query works fine BTW)
Any idea what is going on?
Regards,
Iwaniwanvanderkleijn@.gmail.com wrote:
> Hi there,
> I try to run the following update statement on MS SQL Server 2000 SP4
> UPDATE Site
> SET Active = 0
> WHERE
> RID IN (SELECT site.RID AS site_rid
> FROM Site INNER JOIN
> ManagerSite ms ON site.RID = ms.ArtistSiteID INNER JOIN
> Manager m ON ms.ManagerID = m.RID
> WHERE (am.UserName = 'iwk1004') AND
> (ms.isPrimaryUser = 1))
> Running this query I get the message "Subquery returned more than 1
> value. This is not permitted when the subquery follows =, !=, <, <= ,
> >, >= or when the subquery is used as an expression."
> Which is weird, because according to the documentation and examples I
> inferred could use UPDATE with the use of IN in the condition and the
> subquery returining multiple rows. (sub-query works fine BTW)
> Any idea what is going on?
> Regards,
> Iwan
Your subquery returns multiple rows so change it to return some
aggregation of site.rid.
like
UPDATE Site
SET Active = 0
WHERE
RID IN (SELECT max(site.RID) AS site_rid
FROM Site INNER JOIN
ManagerSite ms ON site.RID = ms.ArtistSiteID INNER JOIN
Manager m ON ms.ManagerID = m.RID
WHERE (am.UserName = 'iwk1004') AND
(ms.isPrimaryUser = 1))
Regards
Amish Shah
http://shahamishm.tripod.com|||Not tested this at all - just an idea
UPDATE Site
SET Active = 0
FROM
(
SELECT site.RID AS site_rid
FROM Site INNER JOIN
ManagerSite ms ON site.RID = ms.ArtistSiteID INNER
JOIN
Manager m ON ms.ManagerID = m.RID
WHERE (am.UserName = 'iwk1004') AND
(ms.isPrimaryUser = 1
) as tRes
WHERE tRes.site_rid = site.RID
/* You could remove site table from the correlated subquery tRes all
together becuase it actually isn't needed and just join
tREs.ArtistSiteID = site.RID */
Or try your original subquery with EXISTS clause rather than IN|||I agree that the message makes no sense with the code shown, since
multiple values are expected after an IN.
This could be written with an EXISTS test using a correlated subquery,
rather than an IN.
UPDATE Site
SET Active = 0
WHERE EXISTS
(SELECT *
FROM ManagerSite ms
JOIN Manager m
ON ms.ManagerID = m.RID
WHERE site.RID = ms.ArtistSiteID
AND am.UserName = 'iwk1004'
AND ms.isPrimaryUser = 1)
Roy Harvey
Beacon Falls, CT
On 7 Aug 2006 03:07:06 -0700, "iwanvanderkleijn@.gmail.com"
<iwanvanderkleijn@.gmail.com> wrote:
>Hi there,
>I try to run the following update statement on MS SQL Server 2000 SP4
>UPDATE Site
>SET Active = 0
> WHERE
> RID IN (SELECT site.RID AS site_rid
> FROM Site INNER JOIN
> ManagerSite ms ON site.RID = ms.ArtistSiteID INNER JOIN
> Manager m ON ms.ManagerID = m.RID
> WHERE (am.UserName = 'iwk1004') AND
>(ms.isPrimaryUser = 1))
>Running this query I get the message "Subquery returned more than 1
>value. This is not permitted when the subquery follows =, !=, <, <= ,
>>, >= or when the subquery is used as an expression."
>Which is weird, because according to the documentation and examples I
>inferred could use UPDATE with the use of IN in the condition and the
>subquery returining multiple rows. (sub-query works fine BTW)
>Any idea what is going on?
>Regards,
>Iwan|||I'm confused about what the [am.] alias is referring to.
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
<iwanvanderkleijn@.gmail.com> wrote in message
news:1154945225.949531.95440@.i42g2000cwa.googlegroups.com...
> Hi there,
> I try to run the following update statement on MS SQL Server 2000 SP4
> UPDATE Site
> SET Active = 0
> WHERE
> RID IN (SELECT site.RID AS site_rid
> FROM Site INNER JOIN
> ManagerSite ms ON site.RID = ms.ArtistSiteID INNER JOIN
> Manager m ON ms.ManagerID = m.RID
> WHERE (am.UserName = 'iwk1004') AND
> (ms.isPrimaryUser = 1))
> Running this query I get the message "Subquery returned more than 1
> value. This is not permitted when the subquery follows =, !=, <, <= ,
>>, >= or when the subquery is used as an expression."
> Which is weird, because according to the documentation and examples I
> inferred could use UPDATE with the use of IN in the condition and the
> subquery returining multiple rows. (sub-query works fine BTW)
> Any idea what is going on?
> Regards,
> Iwan
>|||Arnie Rowland wrote:
> I'm confused about what the [am.] alias is referring to.
>
> --
> Arnie Rowland, Ph.D.
> Westwood Consulting, Inc
> Most good judgment comes from experience.
> Most experience comes from bad judgment.
> - Anonymous
>
> <iwanvanderkleijn@.gmail.com> wrote in message
> news:1154945225.949531.95440@.i42g2000cwa.googlegroups.com...
> > Hi there,
> >
> > I try to run the following update statement on MS SQL Server 2000 SP4
> >
> > UPDATE Site
> >
> > SET Active = 0
> >
> > WHERE
> > RID IN (SELECT site.RID AS site_rid
> > FROM Site INNER JOIN
> > ManagerSite ms ON site.RID = ms.ArtistSiteID INNER JOIN
> > Manager m ON ms.ManagerID = m.RID
> > WHERE (am.UserName = 'iwk1004') AND
> > (ms.isPrimaryUser = 1))
> >
> > Running this query I get the message "Subquery returned more than 1
> > value. This is not permitted when the subquery follows =, !=, <, <= ,
> >>, >= or when the subquery is used as an expression."
> >
> > Which is weird, because according to the documentation and examples I
> > inferred could use UPDATE with the use of IN in the condition and the
> > subquery returining multiple rows. (sub-query works fine BTW)
> >
> > Any idea what is going on?
> >
> > Regards,
> >
> > Iwan
> >
I misunderstand the prob.
Your query looks ok but am is not referencing any table
Regards
Amish Shah|||I think it is confused over what "Site" referance you are trying to
update because it is name twice. Try the following pattern (tables
from Northwind) where you alias the update table.
update o
set o.quantity = 1
from [Order Details] o
where o.OrderId in
(select [Order Details].OrderID
from [Order Details]
inner join dbo.Orders
on [Order Details].Orderid = Orders.OrderID )
JJ