Nrthugu

I've really tried with this problem and it's taking too much of my time now. I just need it to work to demo other features of the program to my students. I and they will not need anything this complex

I have a query where I need to update a table (bids) based on finding a field (design_code) and the minimum of the bid_amount

I have gotten as far as this

UPDATE Bids a 

INNER JOIN (SELECT DesignCode, MIN(Bid_Amount) AS Bid_Amount 

            FROM Bids 

            WHERE DesignCode = "FT1") AS b 

    ON (a.DesignCode=b.DesignCode AND a.Bid_Amount =b.Bid_Amount) 

SET Bid_Currently_Successful = No 

WHERE a.DesignCode = "FT1" AND a.Bid_Amount =b.Bid_Amount; ');

This is based on SQL Update Table Where date = MIN(date)

But I get the error:

Circular reference caused by Bid_Amount in select

I think I know why, duplicate names, but don't know which to change

I also get the error:

Your query does not include the specified expression 'DesignCode' as aprt of an aggregate function.

If I do change the AS Bid_Amount's name

First of all, hello Mr. Lee and welcome to stackoverflow.


Secondly, in the inner query
SELECT DesignCode, MIN(Bid_Amount) AS Bid_Amount FROM Bids WHERE DesignCode = "FT1"


you are trying to use an aggregate function with another column. Usually, to solve this issue, you have to use the group by clause with the column that is not in the aggregate function.

BUT, this changes what you need, as this will be returning the rows as the minimum amount for each DesignCode. That's not what you want.


What you want is the primary key for the row, which has the lowest of all bid amounts, so that, you can update the table bids with it.


So your inner query would be something like this:

SELECT TOP 1 DesignCode, Bid_Amount FROM Bids ORDER BY Bid_Amount

This will return the top row, when ordered in ascending order of Bid_Amount.


So, your overall query would be something like:

UPDATE Bids a

SET Bid_Currently_Successful = 'No'

WHERE

    a.Bidders = (

                    SELECT TOP 1 Bidders 

                    FROM Bids 

                    WHERE DesignCode = 'FT1' 

                    ORDER BY Bid_Amount

                )

    AND a.date_time_of_bid = (

                                 SELECT TOP 1 date_time_of_bid 

                                 FROM Bids 

                                 WHERE DesignCode = 'FT1'

                                 ORDER BY Bid_Amount

                             )

I think, this should work fine.

But I dont think that is a clean way, so, here is another way I would approach this...

UPDATE a

SET a.Bid_Currently_Successful = 'No'

FROM 

    Bids a

    INNER JOIN (

                  SELECT TOP 1

                      Bidders, date_time_of_bid

                  FROM

                      Bids

                  WHERE

                      DesignCode LIKE 'FT1'

                  ORDER BY

                      Bid_Amount

    ) b ON b.Bidders = a.Bidders AND b.date_time_of_bid = a.date_time_of_bid

I think this is a much cleaner and faster way. In this approach I made it sure that the only row, that gets joined with the Bids table is the one that fits your condition.


Though, I should advise, I haven't checked this approach, but it seemed a fun and faster way to do it.

Happy teaching.. :)

EDIT1: In case, you are using MySQL, the query would be:

UPDATE Bids a

SET Bid_Currently_Successful = 'No'

WHERE

    a.Bidders = (

                    SELECT Bidders 

                    FROM Bids 

                    WHERE DesignCode = 'FT1' 

                    ORDER BY Bid_Amount

                    LIMIT 1

                )

    AND a.date_time_of_bid = (

                                 SELECT date_time_of_bid 

                                 FROM Bids 

                                 WHERE DesignCode = 'FT1'

                                 ORDER BY Bid_Amount

                                 LIMIT 1

                             )

and the cleaner approach would be:

UPDATE a

SET a.Bid_Currently_Successful = 'No'

FROM 

    Bids a

    INNER JOIN (

                  SELECT

                      Bidders, date_time_of_bid

                  FROM

                      Bids

                  WHERE

                      DesignCode LIKE 'FT1'

                  ORDER BY

                      Bid_Amount

                  LIMIT 1

    ) b ON b.Bidders = a.Bidders AND b.date_time_of_bid = a.date_time_of_bid

Thanks P.Salmon for the edit.

EDIT2: Thanks for the table details Mr. Lee. Helped me a lot. And also added the where condition, that I seemed to miss.

This should work in MySQL:

UPDATE Bids b JOIN

       (SELECT b.DesignCode, MIN(Bid_Amount) AS Min_Bid_Amount 

        FROM Bids b

        WHERE b.DesignCode = 'FT1'

        GROUP BY b.DesignCode

       ) d

       ON b.DesignCode = d.DesignCode AND 

          b.Bid_Amount = d.Min_Bid_Amount) 

SET b.Bid_Currently_Successful = 'No' 

WHERE b.DesignCode = 'FT1' ;

Most of the changes are cosmetic, but still useful:

• Alias on Bid_Currently_Successful. This is a good practice when using joins in the update.


• Single quotes instead of double quotes around string constants.


• Single quotes around no (unless that is a column in your data).


• 
  GROUP BY in the subquery.

I did it! Thanks for the help guys, it helped me to figure it out. Probably isn't pretty but I've other things to do so I don't care

UPDATE Bids a SET Bid_Currently_Successful = No
WHERE (((a.Bidders)=(SELECT TOP 1 Bidders FROM Bids WHERE Bid_Currently_Successful = Yes AND DesignCode = 'FT1' ORDER BY Bid_Amount))
AND ((a.date_time_of_bid)=(SELECT TOP 1 date_time_of_bid FROM Bids
WHERE Bid_Currently_Successful = Yes AND DesignCode = 'FT1' ORDER BY Bid_Amount)));

All the extra brackets courtesy of Access...