CODEWITHSUNDEEP
sqlt-sqltemp-tables
FireShock
FireShock

Reputation: 1122

How to UPDATE #temptable

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

Upvotes: 3

Views: 73781

Answers (4)

Courageous Chronicler
Courageous Chronicler

Reputation: 33

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.

Upvotes: 1

Prahalad Gaggar
Prahalad Gaggar

Reputation: 11599

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

Upvotes: 2

TechDo
TechDo

Reputation: 18629

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

Upvotes: 6

Nenad Zivkovic
Nenad Zivkovic

Reputation: 18559

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)

Upvotes: 2

Related Questions