Need help designing an update query

[tbone]

I'd like to have a way to add bidders who have won at least $1000 in
the last auction to my mailing list. I'm having trouble with the
update step.

To find bidders who have won at least $1000 in the last auction, I
created this query:

SELECT Sum(Lots.HighBidAmount) AS SumOfHighBidAmount,
Lots.HighBidderNumber
FROM Lots, [Active auctions]
GROUP BY Lots.HighBidderNumber, Lots.AuctionID
HAVING (((Sum(Lots.HighBidAmount))>=1000) AND
((Lots.AuctionID)=First([Active auctions].[AuctionID])));

[Active auctions] is a query that returns a single row identifying the
active auction. The above query, which I named "Bidders winning $1000
minimum" appears to return the correct results.

Next, I figured I'd use the bidder numbers identified in the above
query to select the bidder records to have the mail flag set. I tried
this:

UPDATE [Bidders winning $1000 minimum]
INNER JOIN Bidders
ON [Bidders winning $1000 minimum].HighBidderNumber = Bidders.BidderID
SET Bidders.MailFlag = -1;

I get this error: "Operation must use an updateable query."

I also tried phrasing it the other way around:

UPDATE Bidders
INNER JOIN [Bidders winning $1000 minimum]
ON Bidders.BidderID = [Bidders winning $1000 minimum].HighBidderNumber
SET Bidders.MailFlag = -1;

No luck. I also tried LEFT and RIGHT JOINs, to no avail.

Can someone point me in the right direction?
Thanks
tbone

[salad]

tbone wrote:

I'd like to have a way to add bidders who have won at least $1000 in
the last auction to my mailing list. I'm having trouble with the
update step.

To find bidders who have won at least $1000 in the last auction, I
created this query:

SELECT Sum(Lots.HighBidAmount) AS SumOfHighBidAmount,
Lots.HighBidderNumber
FROM Lots, [Active auctions]
GROUP BY Lots.HighBidderNumber, Lots.AuctionID
HAVING (((Sum(Lots.HighBidAmount))>=1000) AND
((Lots.AuctionID)=First([Active auctions].[AuctionID])));

[Active auctions] is a query that returns a single row identifying the
active auction. The above query, which I named "Bidders winning $1000
minimum" appears to return the correct results.

Next, I figured I'd use the bidder numbers identified in the above
query to select the bidder records to have the mail flag set. I tried
this:

UPDATE [Bidders winning $1000 minimum]
INNER JOIN Bidders
ON [Bidders winning $1000 minimum].HighBidderNumber = Bidders.BidderID
SET Bidders.MailFlag = -1;

I get this error: "Operation must use an updateable query."

I also tried phrasing it the other way around:

UPDATE Bidders
INNER JOIN [Bidders winning $1000 minimum]
ON Bidders.BidderID = [Bidders winning $1000 minimum].HighBidderNumber
SET Bidders.MailFlag = -1;

No luck. I also tried LEFT and RIGHT JOINs, to no avail.

Can someone point me in the right direction?
Thanks
tbone

Can use use something else besides a Totals query? If you use a Totals
query, it is not updateable. Perhaps use Dsum()?

[lesperancer]

update bidders
set mailFlag = -1
where bidderId in (select highBidderNumber FROM [Bidders winning $1000
minimum]);

should work
tbone wrote:

I'd like to have a way to add bidders who have won at least $1000 in
the last auction to my mailing list. I'm having trouble with the
update step.

To find bidders who have won at least $1000 in the last auction, I
created this query:

SELECT Sum(Lots.HighBidAmount) AS SumOfHighBidAmount,
Lots.HighBidderNumber
FROM Lots, [Active auctions]
GROUP BY Lots.HighBidderNumber, Lots.AuctionID
HAVING (((Sum(Lots.HighBidAmount))>=1000) AND
((Lots.AuctionID)=First([Active auctions].[AuctionID])));

[Active auctions] is a query that returns a single row identifying the
active auction. The above query, which I named "Bidders winning $1000
minimum" appears to return the correct results.

Next, I figured I'd use the bidder numbers identified in the above
query to select the bidder records to have the mail flag set. I tried
this:

UPDATE [Bidders winning $1000 minimum]
INNER JOIN Bidders
ON [Bidders winning $1000 minimum].HighBidderNumber = Bidders.BidderID
SET Bidders.MailFlag = -1;

I get this error: "Operation must use an updateable query."

I also tried phrasing it the other way around:

UPDATE Bidders
INNER JOIN [Bidders winning $1000 minimum]
ON Bidders.BidderID = [Bidders winning $1000 minimum].HighBidderNumber
SET Bidders.MailFlag = -1;

No luck. I also tried LEFT and RIGHT JOINs, to no avail.

Can someone point me in the right direction?
Thanks
tbone

[tbone]

Excellent! Works perfectly!

I did not know a SELECT could be used withing a WHERE clause.

Thanks for that valuable lesson!

tbone
On 23 Jan 2006 04:05:54 -0800, le*********@natpro.com wrote:

update bidders
set mailFlag = -1
where bidderId in (select highBidderNumber FROM [Bidders winning $1000
minimum]);