[Proposal] Add ORDER BY and LIMIT to MySQL compileUpdate

[diesse]

Since MySQL syntax for UPDATE statement is the following

UPDATE [LOW_PRIORITY] [IGNORE] table_reference
SET col_name1={expr1|DEFAULT} [, col_name2={expr2|DEFAULT}] ...
[WHERE where_condition]
[ORDER BY ...]
[LIMIT row_count]

method compileUpdate should be extended in Illuminate/Database/Query/Grammars/MySqlGrammar.php

For example:

    public function compileUpdate(Builder $query, $values)
    {
            if ( isset($query->orders) ) {
                    $orders = ' '.$this->compileOrders($query, $query->orders);
            } else {
                    $orders = '';
            }

            if ( isset($query->limit) ) {
                    $limit = ' '.$this->compileLimit($query, $query->limit);
            } else {
                    $limit = '';
            }

            return parent::compileUpdate($query, $values).$orders.$limit;
    }

[taylorotwell]

What is the use case for this in your application?

[diesse]

In mysql it isn't possible to do an update (or a delete, compileDelete should be extended, too) with a subquery referring to the same table you are updating.

For example, if you need to update the row with the lowest id, the statement

UPDATE mytable SET foo = 'bar' WHERE id = (SELECT min(id) from mytable)

returns the following error:

1093 - You can't specify target table 'mytable' for update in FROM clause

In order to execute the update in a single statement you can do:

UPDATE mytable SET foo = 'bar' ORDER BY id ASC LIMIT 1

[taylorotwell]

Done.