UPDATE 存储过程不更新

2022-01-17 00:00:00 sql-update while-loop if-statement sql-server stored-procedures

我有一个 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 行) 消息:

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.

相关文章