Command UPDATE using existing data

Question

I need to update the records of some tables, and I tried using the following command:

UPDATE auction a        
JOIN (SELECT bidding_price, user_id, auction_id FROM bid_account WHERE auction_id = a.auctionID ORDER BY bidpack_buy_date DESC LIMIT 1) ult_lance_a
SET a.auc_final_price=ult_lance_a.bidding_price, a.buy_user=ult_lance_a.user_id
WHERE a.auctionID=ult_lance_a.auction_id;

However I am getting the following error:

#1054 - Column 'a.auctionID' unknown in 'where clause'

what did I do wrong?

Here is the return of the command: SELECT bidding_price, user_id, auction_id FROM bid_account WHERE auction_id = XXX ORDER BY bidpack_buy_date DESC LIMIT 1

bidding_price = 6.23
user_id = 1720
auction_id = 1818

In the auction table, there are the columns:

auc_final_price
bidding_price
auctionID

There are several records, but the records are the same for the columns: auction_id and auctionID.

What I need is to just grab the LAST value entered in the bid_account table, and copy the values to the auction table.

Please explain the logic you want to implement. Sample data and desired results would help. — Gordon Linoff
– Gordon Linoff, Commented Jul 2, 2018 at 1:43
are you sure auction_id exists in both auction and bid_account? — mankowitz
– mankowitz, Commented Jul 2, 2018 at 1:45
The command speaks what I need. I need to update all the records of the auction table by inserting the values values obtained in the JOIN, which are in the bid_account table. — Clebson
– Clebson, Commented Jul 2, 2018 at 1:45
It doesn't really speak to what you need, and it's very hard to back calculate the logic from a query based on the query alone. — Tim Biegeleisen
– Tim Biegeleisen, Commented Jul 2, 2018 at 1:46
@mankowitz No, the columns have different names for each table. auctionID and auction_id — Clebson
– Clebson, Commented Jul 2, 2018 at 1:47

Tim Biegeleisen · Accepted Answer · 2018-07-02 03:16:59Z

2

There is probably a better way to phrase your update, but if you want to use correlated subqueries, then you might need to put them directly into the SET clause:

UPDATE auction a
SET auc_final_price = (SELECT bidding_price
                       FROM bid_account
                       WHERE auction_id = a.auctionID
                       ORDER BY bidpack_buy_date DESC
                       LIMIT 1),
    buy_user = (SELECT user_id
                FROM bid_account
                WHERE auction_id = a.auctionID
                ORDER BY bidpack_buy_date DESC
                LIMIT 1);

Actually, I think we can still write your query using update join syntax. Since your tables are large, you may try joining to a temporary table (in place of a matetialized view):

CREATE TEMPORARY TABLE IF NOT EXISTS 
    auctionView ( INDEX(auction_id) ) 
    ENGINE=MyISAM 
AS (
    SELECT b1.auction_id, b1.bidding_price, b1.user_id
    FROM bid_account b1
    INNER JOIN
    (
        SELECT auction_id, MAX(bidpack_buy_date) AS max_buy_date
        FROM bid_account
        GROUP BY auction_id
    ) b2
        ON b1.auction_id = b2.auction_id AND b1.bidpack_buy_date = b2.max_buy_date
);

UPDATE auction a
INNER JOIN auctionView b
    ON b.auction_id = a.auctionID
SET
    a.auc_final_price = b.bidding_price,
    a.buy_user = b.user_id;

edited Jul 2, 2018 at 3:16

answered Jul 2, 2018 at 1:46

Tim Biegeleisen

526k32 gold badges324 silver badges399 bronze badges

Sign up to request clarification or add additional context in comments.

16 Comments

Tim Biegeleisen Over a year ago

Are you certain that the auction table has an auctionID column?

Clebson Over a year ago

Yes, there is. :/

Tim Biegeleisen Over a year ago

@Wendler I see the problem now. That error is coming from the WHERE clause in your correlated subquery. I don't think your current syntax can work; see my answer for a possible alternative.

Clebson Over a year ago

Here the command did not work, sorry for the lack of information, I updated the question in more detail than I need, can you check it please?

Tim Biegeleisen Over a year ago

did not work ... this does not let me update my answer. What did not work? I gave you 2 queries, did they both fail?

|

SeanW333 · Accepted Answer · 2018-07-02 04:30:39Z

2

Your main problem is that you are missing the "ON" clause in your join and trying to accomplish the same thing using the "WHERE" clause of your subquery.

a.auctionID doesn't exist within the context of the subquery SELECT bidding_price, user_id, auction_id FROM bid_account WHERE auction_id = a.auctionID ..., so it's throwing an error.

Try it like this, instead:

UPDATE 
    auction AS a
INNER JOIN 
    /* Find the highest (max) bidpack_buy_date for each auction */
    (SELECT MAX(bidpack_buy_date) AS `Max Date`, auction_id FROM bid_account GROUP BY auction_id) AS maxdates
    ON 
    maxdates.auction_id = a.auctionID
INNER JOIN
    /* The data from bid_acount that accompanies the max dates found above */
    bid_account AS ult_lance_a
    ON
    ult_lance_a.bidpack_buy_date = maxdates.`Max Date`
    AND
    ult_lance_a.auction_id = maxdates.auction_ID
SET 
    a.auc_final_price = ult_lance_a.bidding_price,
    a.buy_user = ult_lance_a.user_id

Code I used to set up test:

DROP TABLE IF EXISTS bid_account;
CREATE TABLE IF NOT EXISTS bid_account (
    auction_id int,
    user_id int, 
    bidding_price numeric(8,2),
    bidpack_buy_date DATETIME
);

DROP TABLE IF EXISTS auction;
CREATE TABLE IF NOT EXISTS auction (
    auctionID int,
    buy_user int,
    auc_final_price numeric(8,2)
);

INSERT INTO 
    bid_account 
        (auction_id, user_id, bidding_price, bidpack_buy_date)
    VALUES
        (1,1, 10.00, '2017-01-01 12:00:01 PM'),
        (1,2, 20.00, '2017-01-01 12:00:02 PM'),
        (1,3, 30.00, '2017-01-01 12:00:03 PM'),
        (1,4, 40.00, '2017-01-01 12:00:04 PM'),
        (2,1, 10.00, '2017-01-01 12:00:01 PM'),
        (2,2, 20.00, '2017-01-01 12:00:02 PM'),
        (2,3, 30.00, '2017-01-01 12:00:03 PM');

INSERT INTO 
    auction 
        (auctionID)
    VALUES
        (1),
        (2),
        (3),
        (4),
        (5),
        (6),
        (7);

edited Jul 2, 2018 at 4:30

answered Jul 2, 2018 at 1:53

SeanW333

4894 silver badges9 bronze badges

9 Comments

Clebson Over a year ago

Can you explain what it would be: sub.auction_id?

SeanW333 Over a year ago

Sorry, sub.auction_id should be ult_lance_a.auction_id. (I copied from the wrong attempt). My answer is fixed now.

Tim Biegeleisen Over a year ago

There is still a problem with the logic in your update. Look closely at the original correlated subquery and you will see it.

SeanW333 Over a year ago

@TimBiegeleisen Are you referring to the ORDER BY that references a column that's not in the SELECT?

Tim Biegeleisen Over a year ago

No, the WHERE clause in the correlated subquery which you have removed in your answer. It changes the logic.

|

Collectives™ on Stack Overflow

Command UPDATE using existing data

2 Answers 2

16 Comments

9 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

16 Comments

9 Comments

Your Answer

Sign up or log in

Post as a guest

Related