Mysql – How to UPDATE NULL values by MAX()+1

maxMySQLupdate

I want to assign values to null cells of a column as

UPDATE table 1
SET number = MAX(number) + 1
WHERE number IS NULL

but mysql return error Invalid use of group function.

Best Answer

Assuming that you want - if the max(number) in the whole table is say, 27 - to update all the rows with NULL, to 28.

You need a subquery or a derived table to first find this max and then join back to the table:

UPDATE tableX AS t
  CROSS JOIN
    ( SELECT MAX(number) AS max_number
      FROM tableX
    ) AS m
SET t.number = m.max_number + 1
WHERE t.number IS NULL ;

Related Solutions

Mysql – How to COUNT number of rows with LIMIT

It sounds like you want to run the LIMIT, but still know how many rows would have been returned without the LIMIT. Check out FOUND ROWS

Sql-server – How to update ID=null values in table to incremental counter values

You can do this.

WITH T
     AS (SELECT ISNULL((SELECT MAX(ID) FROM StagingTable), 0) + 
                    ROW_NUMBER() OVER (ORDER BY NaturalID) AS New_ID,
                ID
         FROM   StagingTable
         WHERE  ID IS NULL)
UPDATE T
SET    ID = New_ID

So the windowed function is used in the SELECT list but you can still use the result of it to UPDATE the column.

You should probably have a filtered index unique constraint on ID WHERE ID IS NOT NULL to prevent duplicates too. Or run this at serializable isolation level to block concurrent inserts.

Best Answer

Related Solutions

Mysql – How to COUNT number of rows with LIMIT

Sql-server – How to update ID=null values in table to incremental counter values

Related Question