CODEWITHSUNDEEP
amazon-web-servicessql-server-2012amazon-redshift
PyBoss
PyBoss

Reputation: 631

AWS error: Invalid operation: table name "?" specified more than once;

the below code works very well in SQL Server 2012, But when I use it in AWS amazon web service will give me a error "Amazon Invalid operation: table name "#t" specified more than once;"

CREATE TABLE #t (store_id varchar(20),city varchar(20),[state] varchar(20));
INSERT INTO #t VALUES
('22', 'new', 'NY'),
('22', null, null),
('22', null, null),
('33', null, null),
('33', 'LA', 'CA')
;

SELECT DISTINCT store_id, city, [state] 
INTO #unique
FROM #t WHERE city IS NOT NULL;
;

UPDATE #t
SET city = #unique.city, [state] = #unique.[state]
FROM #unique
INNER JOIN #t
ON #unique.store_id = #t.store_id
WHERE #t.city IS NULL

Does anyone know why and modify my code? Thank you.

Upvotes: 0

Views: 781

Answers (1)

demircioglu
demircioglu

Reputation: 3465

Here you go

UPDATE #t
SET city = #unique.city, [state] = #unique.[state]
FROM #unique
WHERE #unique.store_id = #t.store_id
AND #t.city IS NULL

Redshift does not need target table in FROM clause but in case if you need to specify it you need to alias it. 

UPDATE #t
SET city = #unique.city, [state] = #unique.[state]
FROM #unique
JOIN #t t1
ON #unique.store_id = t1.store_id
WHERE t1.city IS NULL

From documentation

If you need to include the target table of the UPDATE statement in the list, use an alias.

https://docs.aws.amazon.com/redshift/latest/dg/r_UPDATE.html

Upvotes: 1

Related Questions