Update multiple fields Accross a Join

Collapse
This topic is closed.
X
X
 
  • Time
  • Show
Clear All
new posts
  • Duane Phillips

    Update multiple fields Accross a Join

    "David Portas" <snipped for brevity> wrote:

    Example 1:[color=blue]
    >
    > UPDATE table_a
    > SET col = ? /* Unspecified */
    > WHERE EXISTS
    > (SELECT *
    > FROM table_b
    > WHERE table_b.key_col = table_a.key_col )
    >[/color]
    <snip again>

    Many thanks. I have used this sample extensively since you posted it. Hope
    you (or someone) can help me with one more thing: How would it be written
    to update several fields in table A with data from table B, where as you
    have shown, a column matches the records?

    TIA!

    ~ Duane Phillips.


  • Erland Sommarskog

    #2
    Re: Update multiple fields Accross a Join

    Duane Phillips (askme@askme.as kme) writes:[color=blue]
    > Many thanks. I have used this sample extensively since you posted it.
    > Hope you (or someone) can help me with one more thing: How would it be
    > written to update several fields in table A with data from table B,
    > where as you have shown, a column matches the records?[/color]

    UPDATE table_a
    SET col1 = b.that_col,
    col2 = b.this_col,
    col3 = b.yet_another_c ol,
    ...
    FROM table_a a
    JOIN table_b b ON a.key_col = b.key_col

    This presumes that for a row in table_a there is at most one matching
    row in table_b.


    --
    Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

    Books Online for SQL Server SP3 at
    Accelerate your AI application's time to market by harnessing the power of your own data and the built-in AI capabilities of SQL Server 2025, the enterprise database with best-in-class security, performance and availability.

    Comment

    • Hugo Kornelis

      #3
      Re: Update multiple fields Accross a Join

      On Thu, 20 Jan 2005 15:57:49 -0700, Duane Phillips wrote:
      [color=blue]
      >"David Portas" <snipped for brevity> wrote:
      >
      >Example 1:[color=green]
      >>
      >> UPDATE table_a
      >> SET col = ? /* Unspecified */
      >> WHERE EXISTS
      >> (SELECT *
      >> FROM table_b
      >> WHERE table_b.key_col = table_a.key_col )
      >>[/color]
      ><snip again>
      >
      >Many thanks. I have used this sample extensively since you posted it. Hope
      >you (or someone) can help me with one more thing: How would it be written
      >to update several fields in table A with data from table B, where as you
      >have shown, a column matches the records?
      >
      >TIA!
      >
      >~ Duane Phillips.
      >[/color]

      Hi Duane,

      The "official" (ANSI-standard) answer is:

      UPDATE table_a
      SET col1 = (SELECT col1
      FROM table_b
      WHERE table_b.key_col = table_a.key_col )
      , col2 = (SELECT col2
      FROM table_b
      WHERE table_b.key_col = table_a.key_col )
      , col3 = (SELECT col3
      FROM table_b
      WHERE table_b.key_col = table_a.key_col )
      WHERE EXISTS (SELECT *
      FROM table_b
      WHERE table_b.key_col = table_a.key_col )

      Unfortunately, SQL Server doesn't optimize this too well. You could also
      use the proprietary T-SQL UPDATE FROM syntax:

      UPDATE table_a
      SET col1 = table_b.col1,
      col2 = table_b.col2,
      col3 = table_b.col3
      FROM table_a
      INNER JOIN table_b
      ON table_b.key_col = table_a.key_col

      The results will only be the same if each row in table a is matched by
      exactly one row in table b; in other cases, the results will be different
      (and the results of the UPDATE FROM syntax may even be undefined)

      Best, Hugo
      --

      (Remove _NO_ and _SPAM_ to get my e-mail address)

      Comment

      • Duane Phillips

        #4
        Re: Update multiple fields Accross a Join

        Thankyou!

        ~ Duane Phillips.

        "Hugo Kornelis" <hugo@pe_NO_rFa ct.in_SPAM_fo> wrote in message
        news:9qh0v0dffd dasmuhcpl83b8eb 86uiep4u1@4ax.c om...[color=blue]
        > On Thu, 20 Jan 2005 15:57:49 -0700, Duane Phillips wrote:
        >[color=green]
        >>"David Portas" <snipped for brevity> wrote:
        >>
        >>Example 1:[color=darkred]
        >>>
        >>> UPDATE table_a
        >>> SET col = ? /* Unspecified */
        >>> WHERE EXISTS
        >>> (SELECT *
        >>> FROM table_b
        >>> WHERE table_b.key_col = table_a.key_col )
        >>>[/color]
        >><snip again>
        >>
        >>Many thanks. I have used this sample extensively since you posted it.
        >>Hope
        >>you (or someone) can help me with one more thing: How would it be written
        >>to update several fields in table A with data from table B, where as you
        >>have shown, a column matches the records?
        >>
        >>TIA!
        >>
        >>~ Duane Phillips.
        >>[/color]
        >
        > Hi Duane,
        >
        > The "official" (ANSI-standard) answer is:
        >
        > UPDATE table_a
        > SET col1 = (SELECT col1
        > FROM table_b
        > WHERE table_b.key_col = table_a.key_col )
        > , col2 = (SELECT col2
        > FROM table_b
        > WHERE table_b.key_col = table_a.key_col )
        > , col3 = (SELECT col3
        > FROM table_b
        > WHERE table_b.key_col = table_a.key_col )
        > WHERE EXISTS (SELECT *
        > FROM table_b
        > WHERE table_b.key_col = table_a.key_col )
        >
        > Unfortunately, SQL Server doesn't optimize this too well. You could also
        > use the proprietary T-SQL UPDATE FROM syntax:
        >
        > UPDATE table_a
        > SET col1 = table_b.col1,
        > col2 = table_b.col2,
        > col3 = table_b.col3
        > FROM table_a
        > INNER JOIN table_b
        > ON table_b.key_col = table_a.key_col
        >
        > The results will only be the same if each row in table a is matched by
        > exactly one row in table b; in other cases, the results will be different
        > (and the results of the UPDATE FROM syntax may even be undefined)
        >
        > Best, Hugo
        > --
        >
        > (Remove _NO_ and _SPAM_ to get my e-mail address)[/color]


        Comment

        • Duane Phillips

          #5
          Re: Update multiple fields Accross a Join

          Thankyou!

          ~ Duane Phillips.

          "Erland Sommarskog" <esquel@sommars kog.se> wrote in message
          news:Xns95E5376 E47CDYazorman@1 27.0.0.1...[color=blue]
          > Duane Phillips (askme@askme.as kme) writes:[color=green]
          >> Many thanks. I have used this sample extensively since you posted it.
          >> Hope you (or someone) can help me with one more thing: How would it be
          >> written to update several fields in table A with data from table B,
          >> where as you have shown, a column matches the records?[/color]
          >
          > UPDATE table_a
          > SET col1 = b.that_col,
          > col2 = b.this_col,
          > col3 = b.yet_another_c ol,
          > ...
          > FROM table_a a
          > JOIN table_b b ON a.key_col = b.key_col
          >
          > This presumes that for a row in table_a there is at most one matching
          > row in table_b.
          >
          >
          > --
          > Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se
          >
          > Books Online for SQL Server SP3 at
          > http://www.microsoft.com/sql/techinf...2000/books.asp[/color]


          Comment

          Working...