65.9K
CodeProject is changing. Read more.
Home

How to use Update query with table variables

starIconstarIconstarIconstarIcon
emptyStarIcon
starIcon

4.80/5 (5 votes)

Apr 27, 2012

CPOL
viewsIcon

52720

Things to remember while using Update query with Table variable

Yesterday I come across a strange issue while I was trying to use update query with table variable. The query which works fine with temporary table does not work with table variables.

Let us confirm this with following example:

First create 2 temporary variables. Use following queries for this:

CREATE TABLE #ProjectTbl (ProjectId INT, ProjectName VARCHAR(50), PartnerId INT, PartnerName VARCHAR(50))

INSERT INTO #ProjectTbl (ProjectId, ProjectName, PartnerId) VALUES (1, 'Project 1', 1)
INSERT INTO #ProjectTbl (ProjectId, ProjectName, PartnerId) VALUES (2, 'Project 2', 1)
INSERT INTO #ProjectTbl (ProjectId, ProjectName, PartnerId) VALUES (3, 'Project 3', 1)
INSERT INTO #ProjectTbl (ProjectId, ProjectName, PartnerId) VALUES (4, 'Project 4', 2)
INSERT INTO #ProjectTbl (ProjectId, ProjectName, PartnerId) VALUES (5, 'Project 5', 2)
INSERT INTO #ProjectTbl (ProjectId, ProjectName, PartnerId) VALUES (6, 'Project 6', 2)
INSERT INTO #ProjectTbl (ProjectId, ProjectName, PartnerId) VALUES (7, 'Project 3', 3)
INSERT INTO #ProjectTbl (ProjectId, ProjectName, PartnerId) VALUES (8, 'Project 4', 3)
INSERT INTO #ProjectTbl (ProjectId, ProjectName, PartnerId) VALUES (9, 'Project 5', 3)
INSERT INTO #ProjectTbl (ProjectId, ProjectName, PartnerId) VALUES (10, 'Project 6', 3)

CREATE TABLE #PartnerTbl (PartnerId INT, PartnerName VARCHAR(50))

INSERT INTO #PartnerTbl (PartnerId, PartnerName) VALUES (1, 'Partner 1')
INSERT INTO #PartnerTbl (PartnerId, PartnerName) VALUES (2, 'Partner 2')
INSERT INTO #PartnerTbl (PartnerId, PartnerName) VALUES (3, 'Partner 3') 

Now try to update the table 1 with a value from table 2 using following query. 

UPDATE
      #ProjectTbl
SET
      #ProjectTbl.PartnerName = #PartnerTbl.PartnerName
FROM
      #PartnerTbl
WHERE
      #ProjectTbl.PartnerId = #PartnerTbl.PartnerId  

It works fine, but when I tried to use the same query with table variables it did not work. Lets see, 

Create 2 table variables and insert some data in the. Use following queries for this: 

DECLARE @ProjectTbl TABLE (ProjectId INT, ProjectName VARCHAR(50), PartnerId INT, PartnerName VARCHAR(50)) 

INSERT INTO @ProjectTbl (ProjectId, ProjectName, PartnerId) VALUES (1, 'Project 1', 1)
INSERT INTO @ProjectTbl (ProjectId, ProjectName, PartnerId) VALUES (2, 'Project 2', 1)
INSERT INTO @ProjectTbl (ProjectId, ProjectName, PartnerId) VALUES (3, 'Project 3', 1)
INSERT INTO @ProjectTbl (ProjectId, ProjectName, PartnerId) VALUES (4, 'Project 4', 2)
INSERT INTO @ProjectTbl (ProjectId, ProjectName, PartnerId) VALUES (5, 'Project 5', 2)
INSERT INTO @ProjectTbl (ProjectId, ProjectName, PartnerId) VALUES (6, 'Project 6', 2)
INSERT INTO @ProjectTbl (ProjectId, ProjectName, PartnerId) VALUES (7, 'Project 3', 3)
INSERT INTO @ProjectTbl (ProjectId, ProjectName, PartnerId) VALUES (8, 'Project 4', 3)
INSERT INTO @ProjectTbl (ProjectId, ProjectName, PartnerId) VALUES (9, 'Project 5', 3)
INSERT INTO @ProjectTbl (ProjectId, ProjectName, PartnerId) VALUES (10, 'Project 6', 3)

DECLARE @PartnerTbl TABLE (PartnerId INT, PartnerName VARCHAR(50))

INSERT INTO @PartnerTbl (PartnerId, PartnerName) VALUES (1, 'Partner 1')
INSERT INTO @PartnerTbl (PartnerId, PartnerName) VALUES (2, 'Partner 2')
INSERT INTO @PartnerTbl (PartnerId, PartnerName) VALUES (3, 'Partner 3')  

Now try to update the table 1 with a value from table 2 using following query.

UPDATE
      @ProjectTbl
SET
      @ProjectTbl.PartnerName = @PartnerTbl.PartnerName
FROM
      @PartnerTbl
WHERE
      @ProjectTbl.PartnerId = @PartnerTbl.PartnerId  

Above queries works fine with temporary tables, but here it gives error. So while working with table variables use following queries in place of above ones. 

UPDATE
      PRJ
SET
      PRJ.PartnerName = PAR.PartnerName
FROM
      @ProjectTbl PRJ, @PartnerTbl PAR
WHERE
      PRJ.PartnerId = PAR.PartnerId 

OR

UPDATE
      PRJ
SET
      PRJ.PartnerName = (SELECT PAR.PartnerName FROM @PartnerTbl PAR WHERE PRJ.PartnerId = PAR.PartnerId)
FROM
      @ProjectTbl PRJ