Click here to Skip to main content
Click here to Skip to main content

Tagged as

How to use Update query with table variables

, 27 Apr 2012 CPOL
Rate this:
Please Sign up or sign in to vote.
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  

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

Praveen Meghwal
Software Developer (Senior)
India India
No Biography provided

Comments and Discussions

 
-- There are no messages in this forum --
| Advertise | Privacy | Terms of Use | Mobile
Web03 | 2.8.150302.1 | Last Updated 27 Apr 2012
Article Copyright 2012 by Praveen Meghwal
Everything else Copyright © CodeProject, 1999-2015
Layout: fixed | fluid