ASPForums.Net RSS Feedhttp://www.aspforums.net/Handlers/RSS.ashxLatest additions to the content that appears on ASPForums.Net(c) 2019 www.aspforums.com. All rights reserved.SQL Server Update Table variable from another table Error: Must declare the scalar variable<p>Hi maideen,</p> <p>For this you have two option.&nbsp;</p> <p>1. Either you have to change the column name&nbsp;SID of Variable table with different name. So that its not going to Ambiguous.</p> <pre class="brush: sql">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, [StID] [varchar](25) NULL, -- Changed with different name. [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,StID,NAME,COURSECODE,AMOUNT,RCVDFOR,STATUS,LOCATION,TAGID) SELECT RCNO,RCDATE,SID,NAME,COURSECODE,AMOUNT,RCVDFOR,STATUS,LOCATION,&#39;A&#39; FROM [dbo].[US_ReceiptDetails] where [sid]=@sid AND [NAME] = @NAME INSERT INTO @Statement (RCNO,RCDATE,StID,NAME,COURSECODE,AMOUNT,RCVDFOR,STATUS,LOCATION,TAGID) SELECT RCNO,RCDATE,SID,NAME,COURSECODE,AMOUNT,RCVDFOR,STATUS,LOCATION,&#39;A&#39; 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 StID = A.SID SELECT * FROM @Statement END</pre> <p>2. Use Temp Table so that you can call&nbsp;column name with table name.</p> <p>Ex:</p> <pre class="brush: sql">CREATE TABLE #Statement ( [id] [bigint] IDENTITY(1,1) NOT NULL, [CustomerId] INT NULL, [Name] [varchar](25) NULL, [Country] [varchar](25) NULL ) INSERT INTO #Statement (CustomerId,Name,Country) SELECT CustomerId,Name,Country FROM [dbo].[Customers] UPDATE #Statement SET Country = A.Country FROM [dbo].[CustomerTest] A WHERE #Statement.CustomerId = A.CustomerId SELECT * FROM #Statement DROP TABLE #Statement</pre> <p>&nbsp;</p>https://www.aspforums.net:443/Threads/441712/SQL-Server-Update-Table-variable-from-another-table-Error-Must-declare-the-scalar-variable/https://www.aspforums.net:443/Threads/441712/SQL-Server-Update-Table-variable-from-another-table-Error-Must-declare-the-scalar-variable/Tue, 30 Oct 2018 05:56:38 GMT