带有子查询的SQL Server VS mySQL更新

A simple question:

一个简单的问题：

The update query below works perfectly in SQL Server but fails in MySQL.

下面的更新查询在SQL Server中完美运行但在MySQL中失败。

MySQL err.msg = "Error Code: 1093. You can't specify target table 'Pos' for update in FROM clause".

I can find several workarounds, but looking for best practice.

我可以找到几个解决方法，但寻找最佳实践。

update Pos set Printed = 1 
where InvoiceNo = 3005 
and Status = 'N' 
and Pos.ItemNo IN 
(select Pos.ItemNo from Pos,ItemMaster 
where invoiceno = 3005 
and status = 'N' 
and printed = 0 
and catType in ('B','L') 
and Pos.itemno = ItemMaster.itemno)

1 个解决方案

#1

Here is the "workaround" I came up with. Since I'm making the call from a .NET application I created a stored procedure that does the JOB.

这是我提出的“解决方法”。因为我正在从.NET应用程序进行调用，所以我创建了一个执行JOB的存储过程。

DELIMITER //
CREATE PROCEDURE UpdatePrinted (IN varInvoiceNo VARCHAR(15))
 BEGIN

    DROP TEMPORARY TABLE IF EXISTS tmpParts;

    CREATE TEMPORARY TABLE tmpParts (tmpItemNo VARCHAR(20) NOT NULL);

    Insert Into tmpParts
    select Pos.ItemNo from Pos,ItemMaster 
    where invoiceno = varInvoiceNo 
    and status = 'N' 
    and printed = 0 
    and catType in ('B','L') 
    and Pos.itemno = ItemMaster.itemno;

    update Pos set Printed = 1 where InvoiceNo = varInvoiceNo and Status = 'N' and Pos.ItemNo IN (Select * from tmpParts);


 END //
DELIMITER ;