Search code examples
phpmysqlsql-delete

Updating a mysql-table. one field = old_value - 1

What I need to do is: - Delete one entry from table formfields - Update EVERY field with ordering > (ordering of the deleted field) - Set the ordering of the updated fields to the current value minus 1 (e.g. if current ordering on the field is 8, then it should be set to 7).

Is that possible in one query? Or how can I make it happen?

Solution

  • You can set up a transaction to do this all at once.

    Given the following table:

    CREATE TABLE `formfields` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(25) NOT NULL,
  `order_no` int(11) NOT NULL,
  PRIMARY KEY (`id`)
);

    You can do something like the following:

    START TRANSACTION;
DELETE FROM formfields WHERE order_no = {YOUR_NUM};
UPDATE formfields SET order_no = order_no - 1 WHERE order_no > {YOUR_NUM};
COMMIT;