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;
Add a comment
|
3 Answers
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;
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
answered Feb 1, 2019 at 9:50
-
actually both answers works for Mysql and i think first answer easier and simple than self join, thank you 😘– Mokh AkhFeb 2, 2019 at 12:50
-
-
-
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– veritasApr 6, 2022 at 15:47
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