错误代码：'where子句‘中的1054个未知列'billId’

Question

尽管有许多类似于此的问题。它和其他的完全不同。我没有办法纠正准确的错误。这就是为什么把它张贴在这里。

我正在编写一个查询，在其中我需要使用一个ID删除多个表上的条目。

我有estimation_id。我想删除bill表，也就是bill_details table.In bill_details tbl，还有bill_id stored.So，我编写了一个查询，以便从bill tbl.But中进行选择，该查询在where子句中使用时出错。

存储过程如下：

CREATE DEFINER=`root`@`localhost` PROCEDURE `delete_by_estimation_no`(in_estimation_id int)
BEGIN
SET SQL_SAFE_UPDATES = 0;

select (id)projId from projects WHERE estimation_no=in_estimation_id; 
DELETE FROM projects WHERE estimation_no=in_estimation_id; 

DELETE FROM estimation WHERE id=in_estimation_id; 
DELETE FROM estimation_details WHERE estimation_id=in_estimation_id;

select (id)billId from bill where estimation=in_estimation_id;
DELETE FROM bill WHERE estimation=in_estimation_id; 
DELETE FROM bill_details WHERE bill_id=billId; 

select (id)invoiceId from invoice where estimation=in_estimation_id;
DELETE FROM invoice WHERE estimation_no=in_estimation_id; 
DELETE FROM invoice_details WHERE invoice_id=invoiceId; 
 
select (id)crId from change_request where project_id=projId;
DELETE FROM change_request WHERE project_id=projId; 
DELETE FROM change_request_details WHERE cr_id=crId; 

END

我得到了这个错误。

错误代码：'where子句‘中的1054个未知列'billId’

[![Bill][1]][1]

​

​

​

​

Answer 1

在billId中没有列bill_details，但是您可以使用一个查询从两个表中删除

    DELETE bill, bill_details
    FROM bill 
    INNER JOIN bill_details ON bill.id = bill_details.bill_id

(假设连接键为id和bill_id)