How to UPDATE #temptable

Question

CREATE TABLE #TempProducts (
    Id uniqueidentifier,
    ManufacturerId uniqueidentifier,
    Number varchar(50),
    PresentId uniqueidentifier null)

How to UPDATE PresentId field? I have different errors in this:

1) There is already an object named '#TempProducts' in the database.

UPDATE #TempProducts
SET #TempProducts.PresentId = p.Id
FROM #TempProducts JOIN Products p ON (#TempProducts.ManufacturerId = p.ManufacturerId AND #TempProducts.Number = p.Number)
WHERE #TempProducts.Id <> p.Id

2) The multi-part identifier "t.PresentId" could not be bound.

UPDATE #TempProducts
SET t.PresentId = p.Id
FROM #TempProducts t JOIN Products p ON (t.ManufacturerId = p.ManufacturerId AND t.Number = p.Number)
WHERE t.Id <> p.Id

Solution 1

For the Second Error please try:

UPDATE t
SET t.PresentId = p.Id
FROM #TempProducts t JOIN Products p ON (t.ManufacturerId = p.ManufacturerId AND t.Number = p.Number)
WHERE t.Id <> p.Id

Solution 2

For problem 1:(Before the CREATE TABLE syntax )

if object_id(tempdb..#TempProducts) is not null
begin
    drop table #TempProducts 
end

And for problem 2 i think @techdo is correct.

UPDATE t
SET t.PresentId = p.Id
FROM #TempProducts t JOIN Products p 
ON (t.ManufacturerId = p.ManufacturerId AND t.Number = p.Number)
WHERE t.Id <> p.Id

Solution 3

UPDATE t
SET t.PresentId = p.Id
FROM #TempProducts t JOIN Products p ON (t.ManufacturerId = p.ManufacturerId AND t.Number = p.Number)
WHERE t.Id <> p.Id

This would be fix for a second error (UPDATE t instead of UPDATE #TempProducts). However, there is no way first query could produce mentioned error.

I am guessing you are executing your CREATE TABLE query again which is producing error because temp table already exists.

You should use a check like this when creating temp tables:

IF (OBJECT_ID('tempdb..#TempProducts') IS NOT NULL) DROP TABLE #TempProducts
CREATE TABLE #TempProducts (
    Id uniqueidentifier,
    ManufacturerId uniqueidentifier,
    Number varchar(50),
    PresentId uniqueidentifier null)

Solution 4

Can't comment due to rep restrictions, but the IF (OBJECT_ID('tempdb..#TempProducts') IS NOT NULL) DROP TABLE #TempProducts" is no longer needed in SQL Server 2012 or higher. You can now use the simpler DROP TABLE IF EXISTS #TempProducts syntax.

View more solutions

Comments

[John Woo]

not my downvote but how did this differ from example 1? using tablename instead of alias is fine.

[TechDo]

@JW :) it's okay. You get The multi-part identifier "t.PresentId" could not be bound. for the second query. While I tried with elias t, it worked (at least error gone).