如何从另一个表更新临时表？ [英] How to update temp table from another table?

问题描述

嗨


我在更新@temp表列中从另一个表列值发出问题。

一旦运行存储过程，下面是错误



Msg 137，Level 16，State 1，Procedure usp_Report_Statement，Line 43 [Batch Start Line 7]必须声明标量变量@Statement。



请给我建议

提前谢谢

Maideen



我的尝试：

Hi
I have in issue in update @temp table column from another table column value.
Once run Stored procedure, below is error

" Msg 137, Level 16, State 1, Procedure usp_Report_Statement, Line 43 [Batch Start Line 7] Must declare the scalar variable "@Statement".

Pls advice me
Thank you in advance
Maideen

What I have tried:

GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


ALTER PROCEDURE [dbo].[usp_Report_Statement]
	@Action VARCHAR(50)=NULL,@sid varchar(25) = NULL,@nric varchar(30) = NULL,@NAME VARCHAR(100) NULL
AS
BEGIN

SET NOCOUNT ON;
   DECLARE @Statement TABLE
    (
		[id] [bigint] IDENTITY(1,1) NOT NULL,
		[RCNO] VARCHAR(10) NULL,
		[RCDATE] DATE NULL,
		[SID] [varchar](25) NULL,
		[NAME] [varchar](50) NULL,
		[NRIC] [varchar](25) NULL,
		[COURSECODE] [varchar](20) NULL,
		[COURSEFEEORI] [varchar](10) NULL,
		[AMOUNT] NUMERIC (18,2) NULL,
		[MODE] VARCHAR(20) NULL,
		[RCVDFOR] VARCHAR(50) NULL,
		[CHQAMT] NUMERIC(18,2) NULL,
		[STATUS] VARCHAR(15) NULL,
		[LOCATION] VARCHAR(10) NULL,
		[TAGID] VARCHAR(5) NULL,
		[INTAKEM] VARCHAR(25) NULL,
		[INTAKEY] VARCHAR(5) NULL

    )
		INSERT INTO @Statement (RCNO,RCDATE,SID,NAME,COURSECODE,AMOUNT,RCVDFOR,STATUS,LOCATION,TAGID) 
		SELECT RCNO,RCDATE,SID,NAME,COURSECODE,AMOUNT,RCVDFOR,STATUS,LOCATION,'A'
		FROM [dbo].[US_ReceiptDetails] where [sid]=@sid AND [NAME] = @NAME 

		INSERT INTO @Statement (RCNO,RCDATE,SID,NAME,COURSECODE,AMOUNT,RCVDFOR,STATUS,LOCATION,TAGID) 
		SELECT RCNO,RCDATE,SID,NAME,COURSECODE,AMOUNT,RCVDFOR,STATUS,LOCATION,'A'
		FROM [dbo].[US_RefundDetails] where [sid]=@sid AND [NAME] = @NAME

		UPDATE @Statement SET INTAKEM = A.INTAKEM, INTAKEY = A.INTAKEY FROM [dbo].[AD_StudentRecord] A
		WHERE @Statement.SID = A.SID

		SELECT * FROM @Statement
	END

推荐答案

您收到错误

UPDATE @Statement SET INTAKEM = A.INTAKEM, INTAKEY = A.INTAKEY FROM [dbo].[AD_StudentRecord] A
		WHERE @Statement.SID = A.SID

如果要使用一个或多个其他值更新表（实际表，表变量或临时表）表，然后你必须 JOIN 表。例如

UPDATE s SET INTAKEM = A.INTAKEM, INTAKEY = A.INTAKEY FROM [dbo].[AD_StudentRecord] A
INNER JOIN @Statement s ON s.SID = A.SID

请注意，我已经 @Stat ement a ALIAS （s） - 据我所知，通过联接更新是必要的（但如果我错了，我很乐意纠正）

Note that I have given @Statement an ALIAS (s) - as far as I know that is necessary when updating via a join (but I'm happy to be corrected if I'm wrong)

这篇关于如何从另一个表更新临时表？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！