Re: Updating a field by the order of onother...

[Felix Geerinckx]

Roberto Cano Ramos wrote:

> I need to update the whole table. Seetting the field ORDER_NUM to the position of the order by
> ID_MEMBER

USE test;
DROP TABLE IF EXISTS control;
CREATE TABLE control (
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
member_id INT NOT NULL,
order_num INT NOT NULL DEFAULT 0
);

INSERT INTO control (member_id) VALUES
(1), (5), (2);

SELECT * FROM control;

SET @t := 0;
UPDATE control
SET order_num = @t := @t + 1
ORDER BY member_id;

SELECT * FROM control;

--
felix
Please use BBCode to format your messages in this forum.