mysql php - how to make one of these two?-CodePudding

I need to execute these two queries from php, is there a way to merge them together in a single query or I have to use a stored procedure?

SET @rn=0;
UPDATE `nl_emails` SET `row_num`=(@rn:=@rn 1);

Thanks in advance

CodePudding user response：

It doesn't look like it is possible. We could create @rn in the query but it will be local and the value will be lost from one row to another.
Here is another way of doing the what I believe you want to do.

create table nl_emails (id int not null primary key ,row_num int);
insert into nl_emails values(10,10),(20,20),(30,30);

with cte as(
select id, row_num, 
row_number() over (order by id)rn
from nl_emails)
update nl_emails join cte on nl_emails.id = cte.id
set nl_emails.row_num = rn;

select * from nl_emails;

id | row_num
-: | ------:
10 |       1
20 |       2
30 |       3

db<>fiddle here

CodePudding user response：

So as question marked with php tag, you can use PHP PDO solution:

<?php

$sql = "SET @rn = 0;
UPDATE nl_emails SET row_num = (@rn:=coalesce(@rn, 0)   1);";

$pdo->exec($sql);

Test PHP PDO online

CodePudding user response：

You can make in one direct query, but you have to check the performance.

Use:

UPDATE `nl_emails` n1 
INNER JOIN (
            SELECT  (@row_number:=@row_number   1) AS num, 
                     id
            FROM  nl_emails, (SELECT @row_number:=0) AS t
) as t1 on n1.id=t1.id
SET n1.`row_num`=t1.num;

https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=bf7d4a9243eed3c3e3aeb934846294b7

The key part is the cross join used

SELECT  (@row_number:=@row_number   1) AS num, 
        id
FROM  nl_emails, (SELECT @row_number:=0) AS t
;