6

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;
10
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 '19 at 12:50
  • awesome! Thanks
    – Normajean
    May 27 at 3:10
6

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

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