Jonathan Edgardo Jonathan Edgardo - 1 month ago 14
MySQL Question

Mysql update with subquery join

I have the update query:

UPDATE cash_billings_bills_articles
SET cash_billings_bills_articles.cashbillingbillarticle_cost = (SELECT articles_pricehistory.articlepricehistory_cost
FROM articles_pricehistory
LEFT JOIN cash_billings_bills
ON cash_billings_bills_articles.cashbillingbill_id = cash_billings_bills.cashbillingbill_id
WHERE articles_pricehistory.article_id = cash_billings_bills_articles.article_id AND
articles_pricehistory.articlepricehistory_date <= cash_billings_bills.cashbillingbill_date
ORDER BY articles_pricehistory.articlepricehistory_date DESC
LIMIT 1
);


But i got the error:
Error Code: 1054. Unknown column 'cash_billings_bills_articles.cashbillingbill_id' in 'on clauseError Code: 1054. Unknown column 'cash_billings_bills_articles.cashbillingbill_id' in 'on clause' 0.000 sec


UPDATE Tables Structures:

enter image description here

Answer

Your join condition seems to be wrong .In your query you try to join tables articles_pricehistory and cash_billings_bills on condition

cash_billings_bills_articles.cashbillingbill_id = cash_billings_bills.cashbillingbill_id

where cash_billings_bills.cashbillingbill_id column should be compared with some column of articles_pricehistory table not cash_billings_bills_articles table.

Your query should be more like

UPDATE cash_billings_bills_articles 
SET 
    cash_billings_bills_articles.cashbillingbillarticle_cost = (SELECT 
            articles_pricehistory.articlepricehistory_cost
        FROM
            articles_pricehistory
                LEFT JOIN
            cash_billings_bills_articles ON cash_billings_bills_articles.article_id = articles_pricehistory.article_id
                LEFT JOIN
            cash_billings_bills ON cash_billings_bills_articles.cashbillingbill_id = cash_billings_bills.cashbillingbill_id
        WHERE
            articles_pricehistory.articlepricehistory_date <= cash_billings_bills.cashbillingbill_date
        ORDER BY articles_pricehistory.articlepricehistory_date DESC
        LIMIT 1)