使用子查询连接进行Mysql更新(Mysql update with subquery join)

使用子查询连接进行Mysql更新(Mysql update with subquery join)

我有更新查询：

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

但是我得到了错误： 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

更新表结构：

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:

最满意答案

您的连接条件似乎是错误的。在您的查询中，您尝试按条件连接表articles_pricehistory和cash_billings_bills

cash_billings_bills_articles.cashbillingbill_id = cash_billings_bills.cashbillingbill_id

其中cash_billings_bills.cashbillingbill_id列应该与articles_pricehistory表中的某一列而不是cash_billings_bills_articles表进行比较 。

你的查询应该更像

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)

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)