问题描述

我有一个 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 表，我的问题就解决了.