By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,847 Members | 2,321 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,847 IT Pros & Developers. It's quick & easy.

Need help designing an update query

P: n/a
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
Jan 21 '06 #1
Share this Question
Share on Google+
3 Replies


P: n/a
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()?
Jan 21 '06 #2

P: n/a

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


Jan 23 '06 #3

P: n/a
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]);

Jan 23 '06 #4

This discussion thread is closed

Replies have been disabled for this discussion.