UPDATE Stored Procedure not Updating

问题:

I've got a SQL Server stored procedure that references a table in my database where users can manually update values for a rent field ('Rent1'). The procedure compares this rent value to a rent field in a different table ('Rent2'). If Rent1 is different from Rent2 the value for Rent2 Is updated to the value of Rent1... or at least that's what is supposed to happen.

When I execute this stored procedure, it runs fine and I receive these output messages:

(1 row(s) affected)


(1 row(s) affected)

Which is the result i'd expect, because as a means of testing, I have changed two values to be different between Rent1 and Rent2. But then when I query my updated table, the values remain unchanged.

Here's my stored procedure:

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

ALTER PROCEDURE update_rent
AS
DECLARE @flag INT
SET @flag = (select COUNT(*) from unit_rent left outer join unittype on unittype = scode where rent <> srent)

WHILE (@flag > 0)

BEGIN

IF (select min(rent) from unit_rent 
    left outer join unittype on unittype = scode 
    left outer join property on property.scode = unit_rent.pscode
    where rent <> srent) <>
   (select min(srent) from unit_rent 
    left outer join unittype on unittype = scode 
    left outer join property on property.scode = unit_rent.pscode
    where rent <> srent
    and rent in (select min(rent) from unit_rent
    left outer join unittype on unittype = scode 
    left outer join property on property.scode = unit_rent.pscode
    where rent <> srent))

BEGIN

UPDATE unittype
SET srent = (select min(rent) from unit_rent 
    left outer join unittype on unittype = scode 
    left outer join property on property.scode = unit_rent.pscode
    where rent <> srent)
WHERE unittype.hmy = (select min(hmy) from unittype left outer join unit_rent on unittype = scode where rent <> srent
        and rent = (select min(rent) from unit_rent left outer join unittype on unittype = scode where rent <> srent))

SET @flag = @flag-1;

END 

END

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

Can anyone see where I might be going wrong or tell me why my output messages are lying to me? Or maybe a different approach I could take? I'd appreciate any form of help, Thanks!

UPDATE: Just tried a different approach, same results, just 3 more (1 row(s) addected) messages:

ALTER PROCEDURE update_rent
AS
DECLARE @tmprent TABLE (hmy INT, rent decimal(11,2));
DECLARE @flag INT
SET @flag = (select COUNT(*) from unit_rent left outer join unittype on unittype = scode where rent <> srent)

INSERT INTO @tmprent (hmy, rent) values (1, 0.00);

WHILE (@flag > 0)

BEGIN

IF (select min(rent) from unit_rent 
    left outer join unittype on unittype = scode 
    left outer join property on property.scode = unit_rent.pscode
    where rent <> srent) <>
   (select min(srent) from unit_rent 
    left outer join unittype on unittype = scode 
    left outer join property on property.scode = unit_rent.pscode
    where rent <> srent
    and rent in (select min(rent) from unit_rent
    left outer join unittype on unittype = scode 
    left outer join property on property.scode = unit_rent.pscode
    where rent <> srent))

BEGIN

UPDATE @tmprent
SET rent = (select min(rent) from unit_rent 
    left outer join unittype on unittype = scode 
    left outer join property on property.scode = unit_rent.pscode
    where rent <> srent)
WHERE hmy = 1

UPDATE unittype
SET srent = (select rent from @tmprent where hmy = 1)
WHERE unittype.hmy = (select min(hmy) from unittype left outer join unit_rent on unittype = scode where rent <> srent
        and rent = (select min(rent) from unit_rent left outer join unittype on unittype = scode where rent <> srent))

SET @flag = @flag-1;

END 

END

回答1:

In the trouble fixing world:

Put a select statement before the Update, to see if anything matches

/*
UPDATE unittype
SET srent = (select min(rent) from unit_rent 
    left outer join unittype on unittype = scode 
    left outer join property on property.scode = unit_rent.pscode
    where rent <> srent)
*/
select * from unittype
WHERE unittype.hmy = (select min(hmy) from unittype left outer join unit_rent on unittype = scode where rent <> srent
        and rent = (select min(rent) from unit_rent left outer join unittype on unittype = scode where rent <> srent))

OR

declare @myCountCheck
select @myCountCheck =
(select count(*)
from unittype
    WHERE unittype.hmy = (select min(hmy) from unittype left outer join unit_rent on unittype = scode where rent <> srent
            and rent = (select min(rent) from unit_rent left outer join unittype on unittype = scode where rent <> srent))
)

if (@myCountCheck < 1)
BEGIN
    print 'No Row Match !!!'
END

EDIT---------------------------------------

If you really want to see what is happening, then code up some "output" auditing...... That way you can capture what is happening in the INSERT/UPDATE statement

http://granadacoder.wordpress.com/2008/12/10/sqlserver20052008-output-clause-in-insertupdatedelete-statements/

Here is the sample code:

SqlServer2005/2008 // OUTPUT clause in INSERT/UPDATE/DELETE statements

These types of samples are all over the place on the web, but here is my original example for which I believe is better clarity.

Original Example(s) at: http://blogs.msdn.com/sqltips/archive/2005/06/13/OUTPUT-clause.aspx

create table PrimaryHolderTable ( i int identity (1001,2) not null primary key, j int not null unique )
create table #OutputResultsHolder ( i int not null, j int not null)

insert into PrimaryHolderTable (j)
output inserted.i, inserted.j into #OutputResultsHolder
select top 10 o.object_id from sys.objects as o order by o.object_id desc –<< from sys.objects is there just to provide some rows


select * from #OutputResultsHolder
drop table #OutputResultsHolder, PrimaryHolderTable;

go



create table dbo.EmployeeTable ( EmpKey int identity(1001,2) ,  EmpAge int not null );
create table dbo.AuditTable ( EntityKey int not null default -1  ,  OldValue int null, NewValue int null , Tag varchar(64)  );

insert into dbo.EmployeeTable (EmpAge)
output inserted.EmpKey , null , inserted.EmpAge , ‘Employee Inserted’ into dbo.AuditTable ( EntityKey , OldValue , NewValue , Tag)
 values( 18 );

insert into dbo.EmployeeTable (EmpAge)
output inserted.EmpKey , null , inserted.EmpAge , ‘Employee Inserted’ into dbo.AuditTable ( EntityKey , OldValue , NewValue , Tag) 
 values( 20 );

insert into dbo.EmployeeTable (EmpAge)
output inserted.EmpKey , null , inserted.EmpAge , ‘Employee Inserted’ into dbo.AuditTable ( EntityKey , OldValue , NewValue , Tag) 
 values( 22 );


update dbo.EmployeeTable
   set EmpAge  = EmpAge + 1
output inserted.EmpKey , deleted.EmpAge, inserted.EmpAge , ‘Employee Updated’ into dbo.AuditTable ( EntityKey , OldValue , NewValue , Tag)
 where EmpAge <=20;

delete from dbo.EmployeeTable
output deleted.EmpKey , deleted.EmpAge, NULL , ‘Employee Deleted’  into dbo.AuditTable (EntityKey , OldValue , NewValue , Tag)
 where EmpAge > 0;–Test multi rows

select * from dbo.EmployeeTable;–<<will be empty at this point
select * from dbo.AuditTable;

drop table dbo.EmployeeTable, dbo.AuditTable;
go

回答2:

I don't know how much my answer could help anyone, but on the off chance it could, i'll include it here..

So my unit_rent table being referenced in my SP, was created by me and populated with data from my unittype table (also referenced in SP). When I populated unit_rent table, I grabbed all the rows from my unittype table. This is where I made my mistake. The unittype table contained multiple units associated with particular unit types, so whenever I'd update one row with my stored procedure, all the other units associated with that unit type would become != to the amount of rent I changed. So I re-populated my unit_rent table with only distinct unit types and my problem was solved.

Pretty silly mistake, but I'd prefer not to leave it unanswered on the off chance it may help someone else.

@granadaCoder - Thanks, Again for your help. Second time you've helped me very thoroughly.