How do I UPDATE from a SELECT in SQL Server - if the select returns multiple items (Follow up question...












2














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









share|improve this question
























  • 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
















2














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









share|improve this question
























  • 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














2












2








2







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









share|improve this question















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






share|improve this question















share|improve this question













share|improve this question




share|improve this question








edited Nov 12 at 10:17









Rahul Neekhra

6021627




6021627










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


















  • 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












3 Answers
3






active

oldest

votes


















2














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.






share|improve this answer





























    0














    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;





    share|improve this answer





























      0














      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





      share|improve this answer





















        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
        });


        }
        });














        draft saved

        draft discarded


















        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









        2














        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.






        share|improve this answer


























          2














          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.






          share|improve this answer
























            2












            2








            2






            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.






            share|improve this answer












            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.







            share|improve this answer












            share|improve this answer



            share|improve this answer










            answered Nov 12 at 10:00









            Tim Biegeleisen

            217k1386139




            217k1386139

























                0














                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;





                share|improve this answer


























                  0














                  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;





                  share|improve this answer
























                    0












                    0








                    0






                    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;





                    share|improve this answer












                    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;






                    share|improve this answer












                    share|improve this answer



                    share|improve this answer










                    answered Nov 12 at 10:15









                    fa06

                    11k2917




                    11k2917























                        0














                        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





                        share|improve this answer


























                          0














                          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





                          share|improve this answer
























                            0












                            0








                            0






                            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





                            share|improve this answer












                            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






                            share|improve this answer












                            share|improve this answer



                            share|improve this answer










                            answered Nov 12 at 10:34









                            Tamir alon

                            102




                            102






























                                draft saved

                                draft discarded




















































                                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.




                                draft saved


                                draft discarded














                                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





















































                                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







                                Popular posts from this blog

                                Florida Star v. B. J. F.

                                Danny Elfman

                                Lugert, Oklahoma