[SQL] Is it possible to update a fields value with the value of the field in the previous row?

Question

I have a table from which I would like the update the value of a certain colum of fields.

Basicly moving one value down and those under it should inherit the previous value of the one about them.

I wonder if this action is possible using a single SQL query.

My table:

CREATE TABLE `menu` (
  `slot_index` int(2) NOT NULL,
  `language_ID` int(2) NOT NULL,
  `menuItem` text NOT NULL,
  PRIMARY KEY (`slot_index`,`language_ID`),
  KEY `language_ID` (`language_ID`)
)  

The content in it:

INSERT INTO `menu` (`slot_index`, `language_ID`, `menuItem`) VALUES
(1, 1, 'Home'),
(2, 1, 'Wie zijn wij'),
(21, 1, 'Missie'),
(22, 1, 'Doelen'),
(23, 1, 'Visie'),
(24, 1, 'Test'),
(3, 1, 'Wat doen wij'),
(31, 1, 'Medische kaart voor op reis'),
(32, 1, 'Huisartsenkaart'),
(33, 1, 'Huisartsenkaart anderstaligen'),
(4, 1, 'Perskamer'),
(5, 1, 'Beheer'),
(6, 1, 'FAQ'),
(7, 1, 'Ervaringen'),
(8, 1, 'Contact'),
(81, 1, 'Disclaimer'),
(9, 1, 'Links'),
(91, 1, 'Adresgegevens')

I would like to move slot_index 5 to 9, and make the fields under it move up inheriting the value from the field above.

Is this possible with a single query at all, or should I just write a script for this?

Thanks in advance.

Wolfert

Answer 1

Maybe using auto increment will help for future uses.

As for the current one I doubt that this can go with single query, because you cant query the table you are trying to update.