AWS error: Invalid operation: table name “?” specified more than once;
.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty{ height:90px;width:728px;box-sizing:border-box;
}
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.
amazon-web-services sql-server-2012 amazon-redshift
asked Nov 29 '18 at 1:31
PyBossPyBoss
717
add a comment |
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.
amazon-web-services sql-server-2012 amazon-redshift
asked Nov 29 '18 at 1:31
PyBossPyBoss
717
add a comment |
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.
amazon-web-services sql-server-2012 amazon-redshift
asked Nov 29 '18 at 1:31
PyBossPyBoss
717
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.
amazon-web-services sql-server-2012 amazon-redshift
amazon-web-services sql-server-2012 amazon-redshift
asked Nov 29 '18 at 1:31
PyBossPyBoss
717
asked Nov 29 '18 at 1:31
PyBossPyBoss
717
edited Nov 29 '18 at 5:51
PyBoss
asked Nov 29 '18 at 1:31
PyBossPyBoss
717
asked Nov 29 '18 at 1:31
PyBossPyBoss
717
asked Nov 29 '18 at 1:31
PyBossPyBoss
717
717
add a comment |
add a comment |
1 Answer
1
active
oldest
votes
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
answered Nov 29 '18 at 21:46
mdem7mdem7
625613
I just don't know why AWS doesn't need a JOIN
– PyBoss
Nov 29 '18 at 22:38
in Redshift JOIN for target is optional using UPDATE, you can also join the target table, updated my answer with more info
– mdem7
Nov 29 '18 at 23:09
add a comment |
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53530594%2faws-error-invalid-operation-table-name-specified-more-than-once%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
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
answered Nov 29 '18 at 21:46
mdem7mdem7
625613
I just don't know why AWS doesn't need a JOIN
– PyBoss
Nov 29 '18 at 22:38
in Redshift JOIN for target is optional using UPDATE, you can also join the target table, updated my answer with more info
– mdem7
Nov 29 '18 at 23:09
add a comment |
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
answered Nov 29 '18 at 21:46
mdem7mdem7
625613
I just don't know why AWS doesn't need a JOIN
– PyBoss
Nov 29 '18 at 22:38
in Redshift JOIN for target is optional using UPDATE, you can also join the target table, updated my answer with more info
– mdem7
Nov 29 '18 at 23:09
add a comment |
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
answered Nov 29 '18 at 21:46
mdem7mdem7
625613
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
answered Nov 29 '18 at 21:46
mdem7mdem7
625613
edited Nov 29 '18 at 23:11
answered Nov 29 '18 at 21:46
mdem7mdem7
625613
answered Nov 29 '18 at 21:46
mdem7mdem7
625613
answered Nov 29 '18 at 21:46
mdem7mdem7
625613
625613
I just don't know why AWS doesn't need a JOIN
– PyBoss
Nov 29 '18 at 22:38
in Redshift JOIN for target is optional using UPDATE, you can also join the target table, updated my answer with more info
– mdem7
Nov 29 '18 at 23:09
add a comment |
I just don't know why AWS doesn't need a JOIN
– PyBoss
Nov 29 '18 at 22:38
in Redshift JOIN for target is optional using UPDATE, you can also join the target table, updated my answer with more info
– mdem7
Nov 29 '18 at 23:09
I just don't know why AWS doesn't need a JOIN
– PyBoss
Nov 29 '18 at 22:38
I just don't know why AWS doesn't need a JOIN
– PyBoss
Nov 29 '18 at 22:38
in Redshift JOIN for target is optional using UPDATE, you can also join the target table, updated my answer with more info
– mdem7
Nov 29 '18 at 23:09
in Redshift JOIN for target is optional using UPDATE, you can also join the target table, updated my answer with more info
– mdem7
Nov 29 '18 at 23:09
add a comment |
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53530594%2faws-error-invalid-operation-table-name-specified-more-than-once%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
