Prestashop is not increasing invoice number

Question

I get below error in order success page IF "next order invoice number" IS NOT set manually from admin.

Fatal error: Uncaught You can't specify target table 'ps_order_invoice' for update in FROM clause<br /><br /><pre>UPDATE `ps_order_invoice` SET number =(SELECT new_number FROM (SELECT (MAX(`number`) + 1) AS new_number FROM `ps_order_invoice`) AS result) WHERE `id_order_invoice` = 54</pre> thrown in /classes/db/Db.php on line 791

I have narrowed error down to classes/order/Order.php

if ($number) {
    Configuration::updateValue('PS_INVOICE_START_NUMBER', false, false, null, $id_shop);
}

$sql = 'UPDATE `'._DB_PREFIX_.'order_invoice` SET number =';

if ($number) {
    $sql .= (int)$number;
} else {
    $sql .= '(SELECT new_number FROM (SELECT (MAX(`number`) + 1) AS new_number
    FROM `'._DB_PREFIX_.'order_invoice`'.(Configuration::get('PS_INVOICE_RESET') ?
        ' WHERE DATE_FORMAT(`date_add`, "%Y") = '.(int)date('Y') : '').') AS result)';
}

$sql .= ' WHERE `id_order_invoice` = '.(int)$order_invoice_id;

return Db::getInstance()->execute($sql);
}

Is this common problem? How to fix this without doing hardcoding to the query ? Prestashop version 1.6.1.6

score 0 · Answer 1 · edited May 23 '17 at 12:08

After 3 min of googling I found solution for you.

from here MySQL Error 1093 - Can't specify target table for update in FROM clause

In MySQL, you can't modify the same table which you use in the SELECT part. This behaviour is documented at: http://dev.mysql.com/doc/refman/5.6/en/update.html

You will need to stop using the nested subquery and execute the operation in two parts, or alternatively use a simple where clause.

from Prestashop forum

https://github.com/PrestaShop/PrestaShop/commit/34ad2f5f45e8b02ce7c3174d94644d6114399746

if ($number) {
            Configuration::updateValue('PS_INVOICE_START_NUMBER', false, false, null, $id_shop);
        }
        $sql = 'UPDATE `'._DB_PREFIX_.'order_invoice` SET number =';
        if ($number) {
            $sql .= (int)$number;
        } else {
            $getNumberSql = '(SELECT new_number FROM (SELECT (MAX(`number`) + 1) AS new_number
            FROM `'._DB_PREFIX_.'order_invoice`'.(Configuration::get('PS_INVOICE_RESET') ?
                ' WHERE DATE_FORMAT(`date_add`, "%Y") = '.(int)date('Y') : '').') AS result)';
            $getNumberSqlRow = Db::getInstance()->getRow($getNumberSql);
            $newInvoiceNumber = $getNumberSqlRow['new_number'];
            $sql .= $newInvoiceNumber;
        }
        $sql .= ' WHERE `id_order_invoice` = '.(int)$order_invoice_id;

Prestashop is not increasing invoice number

1 Answers1