UPDATE存储过程未更新 [英] UPDATE Stored Procedure not Updating

问题描述

我有一个SQL Server存储过程，该存储过程引用了数据库中的一个表，用户可以在其中手动更新rent字段('Rent1')的值.该过程将该租金值与其他表('Rent2')中的租金字段进行比较.如果Rent1与Rent2不同，则Rent2的值将更新为Rent1的值...或者至少应该如此.

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)

这是我期望的结果，因为作为一种测试手段，我更改了两个值，以使Rent1和Rent2之间的值不同.但是当我查询更新后的表时，值保持不变.

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.

这是我的存储过程:

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!

更新:刚刚尝试了一种不同的方法，结果相同，仅再发送3条(1 row(s) addected)消息:

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

推荐答案

我不知道我的答案能对任何人有多大帮助，但是在可能的情况下，我会在这里提供.

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

因此，在我的SP中引用的我的unit_rent表是由我创建的，并填充了我的unittype表中的数据(也在SP中引用).当填充unit_rent表时，我抓住了unittype表中的所有行.这是我犯错的地方. unittype表包含与特定单元类型关联的多个单元，因此，每当我用存储过程更新一行时，与该单元类型关联的所有其他单元将变为!=至我更改的租金金额.因此，我只用不同的单元类型重新填充了unit_rent表，而问题得到了解决.

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-谢谢，再次为您提供帮助.第二次，您已经非常彻底地帮助了我.

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

这篇关于UPDATE存储过程未更新的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！