boo boo - 2 months ago 7
MySQL Question

Update doesn't work in stored procedure

I have a stored procedure to update a column(

LiveStatus
) to 'N' before inserting a new row of data and setting its column(
LiveStatus
) to 'Y' if the
DeviceID
of the new values is already there and its LiveSTatus is 'Y'.

It inserts the new values but the
livestatus
of the previous row with the same
DeviceID
is still 'Y'.

The stored procedure:

DELIMITER $$

CREATE DEFINER=`testmysql`@`%` PROCEDURE `PDADeviceAssign`(

IN DeviceID varchar(50),
IN EmpID varchar(50),
IN PlazaID varchar(50),
IN LaneID varchar(50),
IN AssignDateTime varchar(50),
IN AssignTranID varchar(50),
IN AssignedBy varchar(50),
IN Start_at varchar(50),
IN End_at varchar(50),
IN IsNextDay varchar(50),
IN ClientID varchar(50),
IN Description varchar(50)
)
BEGIN

declare Sno int;

set Sno=(SELECT MAX(Sno) FROM posassignment WHERE DeviceID=DeviceID);
update posassignment set LiveStatus ='N' where Sno=Sno;

Insert into posassignment(DeviceID,EmpId,PlazaID,LaneID,AssignDateTime,AssignTranID,AssignedBy,LiveStatus,Start_at,End_at,IsNextDay,ClientId,Description)
values (DeviceID,EmpID,PlazaID,LaneID,AssignDateTime,AssignTranID,AssignedBy,'Y',Start_at,End_at,IsNextDay,ClientId,Description);
END

Answer

This is your code:

declare Sno int; 

set Sno=(SELECT MAX(Sno) FROM posassignment WHERE DeviceID=DeviceID);    
update posassignment set LiveStatus ='N' where Sno=Sno;

The update is equivalent to:

update posassignment set LiveStatus = 'N' where Sno is not null;

That is, neither sno reference is to the parameter. You have the same problem with DeviceID:

CREATE DEFINER=`testmysql`@`%` PROCEDURE `PDADeviceAssign`(
    IN in_DeviceID varchar(50),
    IN in_EmpID varchar(50),
    . . .
)
BEGIN
    declare v_Sno int; 

    set v_Sno = (SELECT MAX(Sno)
                 FROM posassignment
                 WHERE DeviceID = in_DeviceID
                );  

    update posassignment
        set LiveStatus = 'N'
        where Sno = v_Sno;
Comments