How do I UPDATE from a SELECT in SQL Server - if the select returns multiple items (Follow up question...
up vote
2
down vote
favorite
This is a follow up of the question.
How do I UPDATE from a SELECT in SQL Server?
If I use the query in question 2334712, How would I insert 'ABC', 'BCD' or 'DEF' in the Table_A?
I tried but I can't use the ORDER BY clause in query.
Query result should be ABC (If ascending order), or DEF (If descending order), or based on a key on another column, say B3.
UPDATE
Table_A
SET
Table_A.A2 = Table_B.B2
FROM
Table_A
INNER JOIN Table_B
ON Table_A.A1 = Table_B.B1
WHERE
Table_A.A1 < 10
Table_A:
A1 A2
1 (desired result: can insert 'ABC' or 'DEF' based on my choice)
2
Table_B:
B1 B2 B3
1 ABC 1
1 BCD 2
1 DEF 3
2 GHI 4
sql
sql-server tsql edited Nov 12 at 10:17
Rahul Neekhra
6021627
asked Nov 12 at 9:55
Tony Chan
389
add a comment |
up vote
2
down vote
favorite
This is a follow up of the question.
How do I UPDATE from a SELECT in SQL Server?
If I use the query in question 2334712, How would I insert 'ABC', 'BCD' or 'DEF' in the Table_A?
I tried but I can't use the ORDER BY clause in query.
Query result should be ABC (If ascending order), or DEF (If descending order), or based on a key on another column, say B3.
UPDATE
Table_A
SET
Table_A.A2 = Table_B.B2
FROM
Table_A
INNER JOIN Table_B
ON Table_A.A1 = Table_B.B1
WHERE
Table_A.A1 < 10
Table_A:
A1 A2
1 (desired result: can insert 'ABC' or 'DEF' based on my choice)
2
Table_B:
B1 B2 B3
1 ABC 1
1 BCD 2
1 DEF 3
2 GHI 4
sql
sql-server tsql edited Nov 12 at 10:17
Rahul Neekhra
6021627
asked Nov 12 at 9:55
Tony Chan
389
there are three values in tableB for B1=1. In your question you said "based on my choice" - could you please explain what is your choice
– fa06
Nov 12 at 9:59
The choice can be highest value in B3, lowest value in B3 or specific value in B3 (e.g. B3 = 2)
– Tony Chan
Nov 12 at 10:06
add a comment |
up vote
2
down vote
favorite
up vote
2
down vote
favorite
This is a follow up of the question.
How do I UPDATE from a SELECT in SQL Server?
If I use the query in question 2334712, How would I insert 'ABC', 'BCD' or 'DEF' in the Table_A?
I tried but I can't use the ORDER BY clause in query.
Query result should be ABC (If ascending order), or DEF (If descending order), or based on a key on another column, say B3.
UPDATE
Table_A
SET
Table_A.A2 = Table_B.B2
FROM
Table_A
INNER JOIN Table_B
ON Table_A.A1 = Table_B.B1
WHERE
Table_A.A1 < 10
Table_A:
A1 A2
1 (desired result: can insert 'ABC' or 'DEF' based on my choice)
2
Table_B:
B1 B2 B3
1 ABC 1
1 BCD 2
1 DEF 3
2 GHI 4
sql
sql-server tsql edited Nov 12 at 10:17
Rahul Neekhra
6021627
asked Nov 12 at 9:55
Tony Chan
389
This is a follow up of the question.
How do I UPDATE from a SELECT in SQL Server?
If I use the query in question 2334712, How would I insert 'ABC', 'BCD' or 'DEF' in the Table_A?
I tried but I can't use the ORDER BY clause in query.
Query result should be ABC (If ascending order), or DEF (If descending order), or based on a key on another column, say B3.
UPDATE
Table_A
SET
Table_A.A2 = Table_B.B2
FROM
Table_A
INNER JOIN Table_B
ON Table_A.A1 = Table_B.B1
WHERE
Table_A.A1 < 10
Table_A:
A1 A2
1 (desired result: can insert 'ABC' or 'DEF' based on my choice)
2
Table_B:
B1 B2 B3
1 ABC 1
1 BCD 2
1 DEF 3
2 GHI 4
sql
sql-server tsql sql
sql-server tsql edited Nov 12 at 10:17
Rahul Neekhra
6021627
asked Nov 12 at 9:55
Tony Chan
389
edited Nov 12 at 10:17
Rahul Neekhra
6021627
asked Nov 12 at 9:55
Tony Chan
389
edited Nov 12 at 10:17
Rahul Neekhra
6021627
edited Nov 12 at 10:17
Rahul Neekhra
6021627
edited Nov 12 at 10:17
Rahul Neekhra
6021627
6021627
asked Nov 12 at 9:55
Tony Chan
389
asked Nov 12 at 9:55
Tony Chan
389
asked Nov 12 at 9:55
Tony Chan
389
389
there are three values in tableB for B1=1. In your question you said "based on my choice" - could you please explain what is your choice
– fa06
Nov 12 at 9:59
The choice can be highest value in B3, lowest value in B3 or specific value in B3 (e.g. B3 = 2)
– Tony Chan
Nov 12 at 10:06
add a comment |
there are three values in tableB for B1=1. In your question you said "based on my choice" - could you please explain what is your choice
– fa06
Nov 12 at 9:59
The choice can be highest value in B3, lowest value in B3 or specific value in B3 (e.g. B3 = 2)
– Tony Chan
Nov 12 at 10:06
there are three values in tableB for B1=1. In your question you said "based on my choice" - could you please explain what is your choice
– fa06
Nov 12 at 9:59
there are three values in tableB for B1=1. In your question you said "based on my choice" - could you please explain what is your choice
– fa06
Nov 12 at 9:59
The choice can be highest value in B3, lowest value in B3 or specific value in B3 (e.g. B3 = 2)
– Tony Chan
Nov 12 at 10:06
The choice can be highest value in B3, lowest value in B3 or specific value in B3 (e.g. B3 = 2)
– Tony Chan
Nov 12 at 10:06
add a comment |
3 Answers
3
active
oldest
votes
up vote
2
down vote
Join to a subquery which identifies a single record for each value of B1:
WITH cte AS (
SELECT *, ROW_NUMBER() OVER (PARTITION BY B1 ORDER BY B2) rn
FROM Table_B
)
UPDATE a
SET A2 = b.B2
FROM Table_A a
INNER JOIN cte b
ON a.A1 = b.B1 AND b.rn = 1
WHERE
a.A1 < 10;
This example assumes you want the record having the lowest B2 value for each value of B1. But, you may alter the call to ROW_NUMBER however you want to use a different ordering.
answered Nov 12 at 10:00
Tim Biegeleisen
214k1386135
add a comment |
up vote
0
down vote
You can try below using row_number()
WITH t1 AS (
SELECT *, ROW_NUMBER() OVER (PARTITION BY B1 ORDER BY B3 desc) rownum
FROM Table_B
)
UPDATE a
SET A2 = b.B2
FROM Table_A a
INNER JOIN t1 b
ON a.A1 = b.B1 AND rn=1
WHERE
a.A1 < 10;
OR you can use correlated subquery
UPDATE a
SET A2 = b.B2
FROM Table_A a
INNER JOIN Table_B b
ON a.A1 = b.B1 AND exists (select max(B3) from Table_B c on b.B1 and c.B1)
WHERE
a.A1 < 10;
answered Nov 12 at 10:15
fa06
10.3k1917
add a comment |
up vote
0
down vote
You'll can use 'with' table,
Something like that:
WITH AAA (T_B, ID)
AS
(
SELECT TOP (SELECT COUNT(*) FROM Table_A) Table_B.B2 AS RR ,Table_A.A1
FROM Table_A INNER JOIN Table_B
ON Table_A.A1 = Table_B.B1
WHERE Table_A.A1 < 10
ORDER BY 1 DESC-- OR ASC
)
UPDATE Table_A
SET Table_A.A2 = T_B
FROM AAA
answered Nov 12 at 10:34
Tamir alon
102
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%2f53259651%2fhow-do-i-update-from-a-select-in-sql-server-if-the-select-returns-multiple-ite%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
3 Answers
3
active
oldest
votes
3 Answers
3
active
oldest
votes
active
oldest
votes
active
oldest
votes
up vote
2
down vote
Join to a subquery which identifies a single record for each value of B1:
WITH cte AS (
SELECT *, ROW_NUMBER() OVER (PARTITION BY B1 ORDER BY B2) rn
FROM Table_B
)
UPDATE a
SET A2 = b.B2
FROM Table_A a
INNER JOIN cte b
ON a.A1 = b.B1 AND b.rn = 1
WHERE
a.A1 < 10;
This example assumes you want the record having the lowest B2 value for each value of B1. But, you may alter the call to ROW_NUMBER however you want to use a different ordering.
answered Nov 12 at 10:00
Tim Biegeleisen
214k1386135
add a comment |
up vote
2
down vote
Join to a subquery which identifies a single record for each value of B1:
WITH cte AS (
SELECT *, ROW_NUMBER() OVER (PARTITION BY B1 ORDER BY B2) rn
FROM Table_B
)
UPDATE a
SET A2 = b.B2
FROM Table_A a
INNER JOIN cte b
ON a.A1 = b.B1 AND b.rn = 1
WHERE
a.A1 < 10;
This example assumes you want the record having the lowest B2 value for each value of B1. But, you may alter the call to ROW_NUMBER however you want to use a different ordering.
answered Nov 12 at 10:00
Tim Biegeleisen
214k1386135
add a comment |
up vote
2
down vote
up vote
2
down vote
Join to a subquery which identifies a single record for each value of B1:
WITH cte AS (
SELECT *, ROW_NUMBER() OVER (PARTITION BY B1 ORDER BY B2) rn
FROM Table_B
)
UPDATE a
SET A2 = b.B2
FROM Table_A a
INNER JOIN cte b
ON a.A1 = b.B1 AND b.rn = 1
WHERE
a.A1 < 10;
This example assumes you want the record having the lowest B2 value for each value of B1. But, you may alter the call to ROW_NUMBER however you want to use a different ordering.
answered Nov 12 at 10:00
Tim Biegeleisen
214k1386135
Join to a subquery which identifies a single record for each value of B1:
WITH cte AS (
SELECT *, ROW_NUMBER() OVER (PARTITION BY B1 ORDER BY B2) rn
FROM Table_B
)
UPDATE a
SET A2 = b.B2
FROM Table_A a
INNER JOIN cte b
ON a.A1 = b.B1 AND b.rn = 1
WHERE
a.A1 < 10;
This example assumes you want the record having the lowest B2 value for each value of B1. But, you may alter the call to ROW_NUMBER however you want to use a different ordering.
answered Nov 12 at 10:00
Tim Biegeleisen
214k1386135
answered Nov 12 at 10:00
Tim Biegeleisen
214k1386135
answered Nov 12 at 10:00
Tim Biegeleisen
214k1386135
answered Nov 12 at 10:00
Tim Biegeleisen
214k1386135
214k1386135
add a comment |
add a comment |
up vote
0
down vote
You can try below using row_number()
WITH t1 AS (
SELECT *, ROW_NUMBER() OVER (PARTITION BY B1 ORDER BY B3 desc) rownum
FROM Table_B
)
UPDATE a
SET A2 = b.B2
FROM Table_A a
INNER JOIN t1 b
ON a.A1 = b.B1 AND rn=1
WHERE
a.A1 < 10;
OR you can use correlated subquery
UPDATE a
SET A2 = b.B2
FROM Table_A a
INNER JOIN Table_B b
ON a.A1 = b.B1 AND exists (select max(B3) from Table_B c on b.B1 and c.B1)
WHERE
a.A1 < 10;
answered Nov 12 at 10:15
fa06
10.3k1917
add a comment |
up vote
0
down vote
You can try below using row_number()
WITH t1 AS (
SELECT *, ROW_NUMBER() OVER (PARTITION BY B1 ORDER BY B3 desc) rownum
FROM Table_B
)
UPDATE a
SET A2 = b.B2
FROM Table_A a
INNER JOIN t1 b
ON a.A1 = b.B1 AND rn=1
WHERE
a.A1 < 10;
OR you can use correlated subquery
UPDATE a
SET A2 = b.B2
FROM Table_A a
INNER JOIN Table_B b
ON a.A1 = b.B1 AND exists (select max(B3) from Table_B c on b.B1 and c.B1)
WHERE
a.A1 < 10;
answered Nov 12 at 10:15
fa06
10.3k1917
add a comment |
up vote
0
down vote
up vote
0
down vote
You can try below using row_number()
WITH t1 AS (
SELECT *, ROW_NUMBER() OVER (PARTITION BY B1 ORDER BY B3 desc) rownum
FROM Table_B
)
UPDATE a
SET A2 = b.B2
FROM Table_A a
INNER JOIN t1 b
ON a.A1 = b.B1 AND rn=1
WHERE
a.A1 < 10;
OR you can use correlated subquery
UPDATE a
SET A2 = b.B2
FROM Table_A a
INNER JOIN Table_B b
ON a.A1 = b.B1 AND exists (select max(B3) from Table_B c on b.B1 and c.B1)
WHERE
a.A1 < 10;
answered Nov 12 at 10:15
fa06
10.3k1917
You can try below using row_number()
WITH t1 AS (
SELECT *, ROW_NUMBER() OVER (PARTITION BY B1 ORDER BY B3 desc) rownum
FROM Table_B
)
UPDATE a
SET A2 = b.B2
FROM Table_A a
INNER JOIN t1 b
ON a.A1 = b.B1 AND rn=1
WHERE
a.A1 < 10;
OR you can use correlated subquery
UPDATE a
SET A2 = b.B2
FROM Table_A a
INNER JOIN Table_B b
ON a.A1 = b.B1 AND exists (select max(B3) from Table_B c on b.B1 and c.B1)
WHERE
a.A1 < 10;
answered Nov 12 at 10:15
fa06
10.3k1917
answered Nov 12 at 10:15
fa06
10.3k1917
answered Nov 12 at 10:15
fa06
10.3k1917
answered Nov 12 at 10:15
fa06
10.3k1917
10.3k1917
add a comment |
add a comment |
up vote
0
down vote
You'll can use 'with' table,
Something like that:
WITH AAA (T_B, ID)
AS
(
SELECT TOP (SELECT COUNT(*) FROM Table_A) Table_B.B2 AS RR ,Table_A.A1
FROM Table_A INNER JOIN Table_B
ON Table_A.A1 = Table_B.B1
WHERE Table_A.A1 < 10
ORDER BY 1 DESC-- OR ASC
)
UPDATE Table_A
SET Table_A.A2 = T_B
FROM AAA
answered Nov 12 at 10:34
Tamir alon
102
add a comment |
up vote
0
down vote
You'll can use 'with' table,
Something like that:
WITH AAA (T_B, ID)
AS
(
SELECT TOP (SELECT COUNT(*) FROM Table_A) Table_B.B2 AS RR ,Table_A.A1
FROM Table_A INNER JOIN Table_B
ON Table_A.A1 = Table_B.B1
WHERE Table_A.A1 < 10
ORDER BY 1 DESC-- OR ASC
)
UPDATE Table_A
SET Table_A.A2 = T_B
FROM AAA
answered Nov 12 at 10:34
Tamir alon
102
add a comment |
up vote
0
down vote
up vote
0
down vote
You'll can use 'with' table,
Something like that:
WITH AAA (T_B, ID)
AS
(
SELECT TOP (SELECT COUNT(*) FROM Table_A) Table_B.B2 AS RR ,Table_A.A1
FROM Table_A INNER JOIN Table_B
ON Table_A.A1 = Table_B.B1
WHERE Table_A.A1 < 10
ORDER BY 1 DESC-- OR ASC
)
UPDATE Table_A
SET Table_A.A2 = T_B
FROM AAA
answered Nov 12 at 10:34
Tamir alon
102
You'll can use 'with' table,
Something like that:
WITH AAA (T_B, ID)
AS
(
SELECT TOP (SELECT COUNT(*) FROM Table_A) Table_B.B2 AS RR ,Table_A.A1
FROM Table_A INNER JOIN Table_B
ON Table_A.A1 = Table_B.B1
WHERE Table_A.A1 < 10
ORDER BY 1 DESC-- OR ASC
)
UPDATE Table_A
SET Table_A.A2 = T_B
FROM AAA
answered Nov 12 at 10:34
Tamir alon
102
answered Nov 12 at 10:34
Tamir alon
102
answered Nov 12 at 10:34
Tamir alon
102
answered Nov 12 at 10:34
Tamir alon
102
102
add a comment |
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.
Some of your past answers have not been well-received, and you're in danger of being blocked from answering.
Please pay close attention to the following guidance:
- 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%2f53259651%2fhow-do-i-update-from-a-select-in-sql-server-if-the-select-returns-multiple-ite%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
there are three values in tableB for B1=1. In your question you said "based on my choice" - could you please explain what is your choice
– fa06
Nov 12 at 9:59
The choice can be highest value in B3, lowest value in B3 or specific value in B3 (e.g. B3 = 2)
– Tony Chan
Nov 12 at 10:06