CODEWITHSUNDEEP
mysql
Yusa
Yusa

Reputation: 9

#1054 - Unknown column inner join and update order by

I have 2 tables: items and stock. items has all the information about all the product descriptions and stock, while tbl_stock are the stock are saved. I want to update a column av_stock in tbl stock. Here are my query :

update `stock` inner JOIN
(
   SELECT item_total_stock FROM items 
   ORDER BY item_total_stock DESC
) s
ON items.id = stock.id
set stock.av_stock = `item_total_stock`

But i received error

#1054 - Unknown column 'items.id' in 'on clause'

I've check the table is correct and also the id is correct, any clue?

Upvotes: 0

Views: 62

Answers (2)

yusher
yusher

Reputation: 226

In your inner join sql should have column id

update `stock` a inner JOIN
(
   SELECT id,item_total_stock FROM items 
   ORDER BY item_total_stock DESC
) s
ON s.id = a.id
set a.av_stock = s.`item_total_stock`

Upvotes: 0

Tim Biegeleisen
Tim Biegeleisen

Reputation: 522376

The alias you use in the join has to match the subquery:

UPDATE stock s
INNER JOIN (
    SELECT item_total_stock
    FROM items 
) i
    ON i.id = s.id
SET av_stock = item_total_stock

But, you don't even need a subquery here:

UPDATE stock s
INNER JOIN items i
    ON i.id = s.id
SET av_stock = i.item_total_stock

Upvotes: 2

Related Questions