1

A simple question:

The update query below works perfectly in SQL Server but fails in 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)
Floern
  • 33,559
  • 24
  • 104
  • 119
opWare
  • 11
  • 1
  • Not an answer to your question but you should use the ANSI-92 style join syntax...it has been almost 30 years. https://sqlblog.org/2009/10/08/bad-habits-to-kick-using-old-style-joins?_sm_au_=iHV6rWRnZFqvsNZN – Sean Lange Feb 03 '16 at 19:32
  • Yep; Old habits are hard to break. . . – opWare Feb 03 '16 at 20:28
  • please take a look at: http://stackoverflow.com/questions/4268416/sql-update-with-sub-query-that-references-the-same-table-in-mysql – manRo Feb 03 '16 at 21:49
  • SQL Server handles it, no problem, MySQL not so. Wrong approach perhaps; I now created a Stored Procedure that works best. . . Thanks for the link. . . – opWare Feb 03 '16 at 23:18

1 Answers1

0

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.

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 ;
opWare
  • 11
  • 1