update in chain

Question

update in chain

Navigation

#1 by (1 votes)

3

I have a table that has several tasks, the idea is to "finish one" and start another, but I do not know how to formulate a query like this

I have the following:

create table tareas( 
id int not null PRIMARY key AUTO_INCREMENT, 
idvinedo int not null, idplantacion int not null, 
nombre varchar(50) not null, 
duracion varchar(50) not null, 
orden int not null, 
FOREIGN KEY (idvinedo) REFERENCES vinedo(id), 
FOREIGN KEY (idplantacion) REFERENCES plantaciones(id));

The idea is that active goes to 0 and to 1 in the next row, order keeps correlatively the tasks, so it can be used to guide which is the next row to change

mysql mariadb

asked by zhet 01.10.2018 в 05:23

source

1 answer

convert mysql_connect to mysqli_connect Load data in a text box from sql, when changing a combo box or a DataTimePicker in C #, Visual Studio MVC

score 1 · Answer 1

You need two updates. Assuming that the order is unique and correlative, you can update the active field according to the value of the order of the task that is currently active and has been completed:

To get the order of the active task (You could also add the id as a parameter if you know it to ensure a better query):

select orden from tareas where activo = 1;

To mark the current one as finished and move on to the next:

update tareas set activo = 0 where orden = $orden;
update tareas set activo = 1 where orden = $orden + 1;

All these queries could go in a transaction to prevent the data from being modified from another process

EDIT: If you need to know if there is another task after the current one, you can search for the order if there is any record that is later. This you would do before doing the second update:

select orden from tareas where orden > $orden;