sql - 子查询返回了 1 个以上的值。无法解决

Question

第二次问这个问题了，还是找不到答案，求助。我想用这个查询更新我的“MakinelerVeParcalar”表；

UPDATE MakinelerVeParcalar SET Durum = 'Montaj' WHERE ID = 161

我得到了这个错误；

Msg 512, Level 16, State 1, Procedure trgSureUpdate, Line 31 [Batch Start Line 0] Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

我的 trgSureUpdate(触发器)

ALTER TRIGGER [dbo].[trgSureUpdate]
            ON [dbo].[MakinelerVeParcalar]
            AFTER UPDATE
            AS 
            BEGIN

                DECLARE @count INT
                DECLARE @idinserted INT
                DECLARE @duruminserted VARCHAR(50)
                DECLARE @max INT
                DECLARE @iddeleted INT
                DECLARE @durumdeleted VARCHAR(50)
                DECLARE @datediff INT
                DECLARE @durumbilgisi varchar(50)
                DECLARE @sureinserted INT
                DECLARE @suredeleted INT
                DECLARE @diffdate INT

                SELECT @idinserted = ID from inserted  <-- Line 31
                SELECT @duruminserted = Durum from inserted
                SELECT @iddeleted = ID from deleted
                SELECT @durumdeleted = Durum from deleted

                SET @count = (SELECT count(*) FROM Sure WHERE ID = @idinserted and Asama = @duruminserted)
                SET @max = (SELECT max(SiraNo) FROM Sure WHERE ID = @idinserted)
                SET @durumbilgisi = (SELECT DurumBilgisi FROM DurumBilgisi WHERE ID = @idinserted)
                SET @sureinserted = (SELECT Sure FROM Sure WHERE ID = @idinserted and Asama = @duruminserted)
                SET @suredeleted = (SELECT Sure FROM Sure WHERE ID = @iddeleted and Asama = @durumdeleted)

                IF @duruminserted != @durumdeleted
                BEGIN


                    IF @durumbilgisi != 'Bitti'
                    BEGIN
                        UPDATE Sure Set Cikis = GETDATE() WHERE Asama = @durumdeleted and ID = @idinserted and SiraNo = @max
                        SET @diffdate = DATEDIFF (SECOND,(SELECT Giris FROM Sure WHERE ID = @idinserted and SiraNo = @max),(SELECT Cikis FROM Sure WHERE ID = @idinserted and SiraNo = @max))
                        UPDATE Sure SET Sure = (@diffdate) WHERE ID = @idinserted and SiraNo = @max
                    END
                    INSERT INTO Sure (ID,Asama,Giris,Cikis,Sure,SiraNo) VALUES(@idinserted,@duruminserted,GETDATE(),NULL,0,(@max+1))
                    UPDATE DurumBilgisi SET DurumBilgisi = 'Devam Ediyor' WHERE ID = @idinserted
                END
            END

如果您需要表格，我可以发送所有表格。谢谢...

Answer 1

您需要使用top 子句限制子查询 中的行数:

 SELECT TOP (1) @durumbilgisi = DurumBilgisi 
 FROM DurumBilgisi 
 WHERE ID = @idinserted
 ORDER BY ??

 SELECT TOP (1) @sureinserted = Sure 
 FROM Sure 
 WHERE ID = @idinserted and Asama = @duruminserted
 ORDER BY ??

 SELECT TOP (1) @suredeleted = Sure 
 FROM Sure 
 WHERE ID = @iddeleted and Asama = @durumdeleted
 ORDER BY ??

?? 指定列排序的排序列。