8

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

14
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)
ORDER BY id;
2
  • that works perfectly with simple code 😘
    – Mokh Akh
    Feb 2, 2019 at 12:50
  • awesome! Thanks
    – Normajean
    May 27, 2021 at 3:10
7

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:

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

Tested in dbfiddle.uk

5
  • actually both answers works for Mysql and i think first answer easier and simple than self join, thank you 😘
    – Mokh Akh
    Feb 2, 2019 at 12:50
  • @MokhAkh both answers? I only provided one. Feb 3, 2019 at 13:28
  • i mean answer from Akina above ;)
    – Mokh Akh
    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. 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
    Apr 6 at 15:47
1

I got the answer :)

UPDATE contact m
join 
(
   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, privacy policy and cookie policy

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