i have table
Item | Process | Number | Saved
A | 1 | 11,20 | Yes
A | 2 | 11,20 | Yes
A | 3 | 11,20 | Yes
A | 1 | 1,2 | No
A | 2 | 1,2 | No
A | 3 | 1,2 | No
B | 1 | 5,7,8 | No
B | 2 | 5,7,8 | No
if i give input item & number, then it will update the number base on the item on all process if saved field is "no".
so if my item input was A & the number is 4 then the result on field number i want is 1,2,4.
my code logic is like this.
number filtering to prevent multiple input on the same number.
return valid number only
select for process table (to get all the process)
looping for each row
update number into on table where item=A, Proccess=, and saved='no'
end loop
the problem when it execute in many device, like
Device 1 send item A & number 4
Device 2 send item A & Number 8
There is a time when it getting mixed up each other end up random update. like
A | 1 | 1,2,4,8 | No
A | 2 | 1,2,8 | No
A | 3 | 1,2,8 | No
my suspicious is on query queue like :
update to 1,2,4 on process 1 (device 1)
update to 1,2,4,8 on process 1 (device 2)
update to 1,2,4 on process 2 (device 1)
update to 1,2,8 on process 2 (device 2) ***
*** upon filtering, the lines havent been updated so it still check for 1,2 & adding 8 as a valid input.
then the update occure from device 1 become 1,2,4
and then it overwrite by update into 1,2,8
hope my explainantion is not confusing.
is there a way to prevent this?
my idea currently is to move filtering on each process select row but my concern is the time it takes to do the same/similar query multiple times.
or can update statement used with select at once like insert select? and does sql process it as 1 queue or each row on select split into different queue.
thanks.