Create an update from an if or a case in sql or postgresql

Question

Create an update from an if or a case in sql or postgresql

Navigation

#1 by (1 votes)
#2 by (0 votes)

0

I'm doing an inventory program for a project with RFID. The problem is having the inputs and outputs controlled.

When something comes out of the store, it does not necessarily come out permanently so it can re-enter, the problem is that the outputs and inputs are detected by the sensors, the table of this part I have it in the following way.

Numero_Caja | Date_Entry | Departure date | Existence

In which the box number is the primary key and existence is Boolean when a new box is entered the number plus date of entry is added and when this same box enters the date of departure is added and the existence changes to false, as the box can re-enter I want the table to delete the date of departure and the state of existence change to true if this re-enters, and to remove from the table the boxes that did not re-enter a button was added that will eliminate all that in existence are in false.

Once the context has been defined, I have been trying for some time to re-enter the state of existence and if it comes out again it will change again I have a small approach with the following:

UPDATE market 
SET exitdate = CASE WHEN existence = true AND boxnumm = caja que lee el sensor then now() else null END,
    existence = CASE WHEN exitdate <> now() AND boxnumm = caja que lee el sensor then false else true END
;

but similarly it works halfway since it has to be executed twice to work and works badly since it changes the status and date of the current box but those of the others return it to true and with the date in null.

If you happen to another way how to pass the data of existence to a variable in the program would also help me.

Thanks in advance

sql c# postgresql

asked by R. Nuñez 10.01.2018 в 18:53

source

2 answers

1

I already solve it, I do not know if it's the right thing to do, but it's working with the following

UPDATE market 
SET exitdate = CASE WHEN existence = true AND boxnumm = xxxx then now() else null END,
    existence = CASE WHEN exitdate <> null AND boxnumm = xxxx then false else true END
    WHERE boxnumm = xxx
;

the only thing that I have to execute twice and already do what I wanted

answered by 10.01.2018 в 19:52

Do not log in even if the data is correct - PHP Hover event that works in javascript and not in css

score 0 · Accepted Answer

But you would not have to execute it twice. Effectively the comparison with the box number that the sensor reads should be in the clause WHERE to select the record to be updated.

In UPDATE you should update the 3 fields depending on whether the existence field is set to true or false .

If existence is true it is an exit and therefore the date of entry is left as it is, the date of departure is established with the current date and existence passes to false .

If existence is false it is an entry and therefore the entry date is set to the current date, the exit date to NULL and existence goes to true

UPDATE market
SET entryDate = CASE WHEN existence THEN entryDate ELSE NOW() END,
    exitDate = CASE WHEN existence THEN NOW() ELSE NULL END,
    existence = CASE WHEN existence THEN FALSE ELSE TRUE END
WHERE boxnum = caja que lee el sensor