Is there any easy way to update Column by Row number not a PK ex:

UPDATE contact m
SET    ContactNumber = sub.rn + 500
FROM  (SELECT Id, row_number() OVER (ORDER BY Id) AS rn FROM contact) sub
WHERE  m.Id = sub.Id;

3 Answers 3

UPDATE contact
SET ContactNumber = (@rownum := 1 + @rownum) + 500
WHERE 0 = (@rownum:=0)
-- for a given client, for example, Client_Id=123, use
-- WHERE Client_Id = 123 + (@rownum:=0)
  • that works perfectly with simple code 😘
    – Mokh Akh
    Commented Feb 2, 2019 at 12:50

The syntax for using joins in UPDATE in MySQL and MariaDB is: UPDATE a JOIN b ON ... SET ... WHERE ... ;. The syntax you used UPDATE .. SET ... FROM ... WHERE ... ; is used in SQL Server.

For recent versions of MariaDB that have window/ranking functions, the following will work:

    contact AS m
    ( SELECT Id, row_number() OVER (ORDER BY Id) AS rn 
      FROM contact
    ) AS sub
  ON  m.Id = sub.Id
    m.ContactNumber = sub.rn + 500

Tested in dbfiddle.uk

  • actually both answers works for Mysql and i think first answer easier and simple than self join, thank you 😘
    – Mokh Akh
    Commented Feb 2, 2019 at 12:50
  • @MokhAkh both answers? I only provided one. Commented Feb 3, 2019 at 13:28
  • i mean answer from Akina above ;)
    – Mokh Akh
    Commented Feb 7, 2019 at 20:13
  • Yes, Akina's answer is simpler. It uses non-standard SQL (variables and ORDER BY in UPDATE) but it will probably perform better than mine. It's also easier to break (in an upgrade) and stop working unexpectedly, so you have to keep some tests around it. Commented Feb 7, 2019 at 21:00
  • in my case, I had to add the userid(primary key) of the respective row, back to the lastname. m.ContactNumber = concat(lastname, sub.userID) worked for
    – veritas
    Commented Apr 6, 2022 at 15:47

I got the answer :)

UPDATE contact m
   SELECT Id, @rownum:=@rownum+1 rownum FROM contact
   CROSS JOIN (select @rownum := 0) rn
   order by contact.Id
) as sub on sub.Id= m.Id 
set m.ContactNumber = sub.rownum + 500
where m.Client_Id=1

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.