Command UPDATE using existing data

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

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:

auction

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.

bid_account

auction

auction_id

auction

bid_account

auction

bid_account

auctionID and auction_id

2 Answers
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:

SET

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;


auction

auctionID

WHERE

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.

SELECT bidding_price, user_id, auction_id FROM bid_account WHERE auction_id = a.auctionID ...

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);


sub.auction_id

sub.auction_id

ult_lance_a.auction_id

WHERE

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.