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 wrote:[color=blue]
> 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[/color]

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

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

should work
tbone wrote:[color=blue]
> 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[/color]

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, lesperancer@natpro.com wrote:
[color=blue]
>
>update bidders
> set mailFlag = -1
> where bidderId in (select highBidderNumber FROM [Bidders winning $1000
>minimum]);
>[/color]