Professional Web Applications Themes

SQL Question: Update using correlated subquery? - Microsoft SQL / MS SQL Server

Using the query below, I want to update *every* record in Table1 with a corresponding value in Table2. If there is no corresponding record in Table2 I want Table1.Column2 set to NULL. Will this work? UPDATE Table1 SET Column2 = ( SELECT Column2 FROM Table2 WHERE Table2.Column1=Table1.Column1 )...

  1. #1

    Default SQL Question: Update using correlated subquery?

    Using the query below, I want to update *every* record in Table1 with a
    corresponding value in Table2. If there is no corresponding record in Table2
    I want Table1.Column2 set to NULL. Will this work?

    UPDATE Table1 SET Column2 =
    (
    SELECT Column2 FROM Table2
    WHERE Table2.Column1=Table1.Column1
    )


    John Guest

  2. #2

    Default Re: SQL Question: Update using correlated subquery?

    Try,

    update t1
    set t1.column2 = t2.column2
    from table1 as t1 left join table2 as t2 on t1.column1 = t2.column1



    AMB


    Alejandro Guest

  3. #3

    Default Re: SQL Question: Update using correlated subquery?

    Thanks Amy. I know my query runs, so can you tell me what it IS doing
    please?

    "Amy" <com> wrote in message
    news:phx.gbl... 
    > Table2 
    >
    >[/ref]


    John Guest

  4. #4

    Default Re: SQL Question: Update using correlated subquery?

    Hi John,
    Your Syntax is incorrect.

    Try:

    UPDATE T1
    SET Column2 = T2.Column2
    FROM Table1 AS T1
    LEFT OUTER JOIN
    Table2 AS T2
    ON T1.Column1 = T2.Column1

    HTH

    "John Smith" <com> wrote in message
    news:%phx.gbl... 
    Table2 


    Amy Guest

  5. #5

    Default Re: SQL Question: Update using correlated subquery?

    >> Hi John,Your Syntax is incorrect. <<

    Wrong. He is correct and yours is a proprietary nightmare.

    SQL Server, Sybase and Ingres allow the FROM clause in the UPDATE, but
    with **different** semantics. Here is how this works in Standard SQL:

    There is no FROM clause in a Standard SQL UPDATE statement; it would
    make no sense. Other products also use the UPDATE .. FROM syntax, but
    with different semantics. So it does not port, or even worse, when you
    do move it, it trashes your database. Other programmers cannot read it
    and maintaining it is harder. And when Microsoft decides to change it,
    you will have to do a re-write. Remember the deprecated "*=" versus
    "LEFT OUTER JOIN" conversions?

    The correct syntax for a searched update statement is

    <update statement> ::=
    UPDATE <table name>
    SET <set clause list>
    [WHERE <search condition>]

    <set clause list> ::=
    <set clause> [{ , <set clause> }...]

    <set clause> ::= <object column> = <update source>

    <update source> ::= <value expression> | NULL | DEFAULT

    <object column> ::= <column name>

    The UPDATE clause simply gives the name of the base table or updatable
    view to be changed.

    Notice that no correlation name is allowed in the UPDATE clause; this is
    to avoid some self-referencing problems that could occur. But it also
    follows the data model in Standard SQL. When you give a table expression
    a correlation name, it is to act as if a materialized table with that
    correlation name has been created in the database. That table then is
    dropped at the end of the statement. If you allowed correlation names
    in the UPDATE clause, you would be updating the materialized table,
    which would then disappear and leave the base table untouched.

    The SET clause is a list of columns to be changed or made; the WHERE
    clause tells the statement which rows to use. For this discussion, we
    will assume the user doing the update has applicable UPDATE privileges
    for each <object column>.

    * The WHERE Clause

    As mentioned, the most important thing to remember about the WHERE
    clause is that it is optional. If there is no WHERE clause, all rows in
    the table are changed. This is a common error; if you make it,
    immediately execute a ROLLBACK statement.

    All rows that test TRUE for the <search condition> are marked as a
    subset and not as individual rows. It is also possible that this subset
    will be empty. This subset is used to construct a new set of rows that
    will be inserted into the table when the subset is deleted from the
    table. Note that the empty subset is a valid update that will fire
    declarative referential actions and triggers.

    * The SET Clause

    Each assignment in the <set clause list> is executed in parallel and
    each SET clause changes all the qualified rows at once. Or at least
    that is the theoretical model. In practice, implementations will first
    mark all of the qualified rows in the table in one pass, using the WHERE
    clause. If there were no problems, then the SQL engine makes a copy of
    each marked row in working storage. Each SET clause is executed based
    on the old row image and the results are put in the new row image.
    Finally, the old rows are deleted and the new rows are inserted. If an
    error occurs during all of this, then system does a ROLLBACK, the table
    is left unchanged and the errors are reported. This parallelism is not
    like what you find in a traditional third-generation programming
    language, so it may be hard to learn. This feature lets you write a
    statement that will swap the values in two columns, thus:

    UPDATE MyTable
    SET a = b, b = a;

    This is not the same thing as

    BEGIN ATOMIC
    UPDATE MyTable
    SET a = b;
    UPDATE MyTable
    SET b = a;
    END;

    In the first UPDATE, columns a and b will swap values in each row. In
    the second pair of UPDATEs, column a will get all of the values of
    column b in each row. In the second UPDATE of the pair, a, which now
    has the same value as the original value of b, will be written back into
    column b -- no change at all. There are some limits as to what the
    value expression can be. The same column cannot appear more than once
    in a <set clause list> -- which makes sense, given the parallel nature
    of the statement. Since both go into effect at the same time, you would
    not know which SET clause to use.

    If a subquery expression is used in a <set clause>, and it returns a
    single value, the result set is cast to a scalar; if it returns an
    empty, the result set is cast to a NULL; if it returns multiple rows, a
    cardinatliy violation is raised.



    --CELKO--
    ===========================
    Please post DDL, so that people do not have to guess what the keys,
    constraints, Declarative Referential Integrity, datatypes, etc. in your
    schema are.

    *** Sent via Developersdex http://www.developersdex.com ***
    Don't just participate in USENET...get rewarded for it!
    Joe Guest

  6. #6

    Default Re: SQL Question: Update using correlated subquery?

    Hi John,

    The LEFT OUTER JOIN produces a result 'table' in which for every row in
    table1, the matching row from table 2 is 'attached' and for every row from
    table1 for which no match was found, NULL values are attached instead for
    every column from table2.
    If you run from query yzer:

    SELECT *
    FROM Table1 AS T1
    LEFT OUTER JOIN
    Table2 AS T2
    ON T1.Column1 = T2.Column1

    you will see this result 'table' that the join produces.
    now, when the rows are matched, we update table1.column2 with the matching
    table2.column2.
    An INNER JOIN would simply not return any rows that had no match.
    For the complete syntax and examples, look in BOL for 'UPDATE' and 'joining
    tables'

    HTH

    "John Smith" <com> wrote in message
    news:phx.gbl... [/ref][/ref]

    > > Table2 
    > >
    > >[/ref]
    >
    >[/ref]


    Amy Guest

  7. #7

    Default Re: SQL Question: Update using correlated subquery?

    I'm not sure what you're getting at Amy. John's query does exactly what he
    has asked for - it populates Table1.Column2 where a matching value exists
    and if no value exists Column2 is populated with NULLs.

    Your answer is also valid but the MS proprietary syntax you used is badly
    flawed and sometimes actually dangerous (Microsoft's fault not yours!). My
    own recommendation would be to use the ANSI standard syntax which is what
    John posted.

    Hope this helps. :-)

    --
    David Portas
    ------------
    Please reply only to the newsgroup
    --


    David Guest

  8. #8

    Default Re: SQL Question: Update using correlated subquery?

    There are missing criteria in John's question.
    If the relationship between Table1 and Table2 is 1:M, the subquery solution
    would fail provided that at least one col1 occurrence has multiple
    occurrences in Table2. Since john didn't specify that the relationship is
    1:1, we can't just assume that it is.
    The LEFT JOIN solution would work in a 1:M relationship but it is not
    deterministic; you can't tell which of the different values of col2 per col1
    would be picked.
    In a 1:1 relationship, both solutions produce the same results.
    So. John, please specify whether the relationship is 1:M or 1:1. And also,
    in a 1:M case, please specify which of the different col2 values you'd like
    to be picked (e.g., MIN, MAX, etc.). For example, in a 1:M relationship,
    with a MAX() criteria for col2 values, the following would work:

    UPDATE Table1
    SET col2 = (SELECT MAX(col2) FROM Table2
    WHERE Table2.col1 = Table1.col1)

    Otherwise, as I mentioned earlier, both solutions are the same, and both are
    deterministic. Pick your choose according to whether you mind using
    proprietary code or not, and also be aware of the implications specified by
    others of using proprietary code.


    --
    BG, SQL Server MVP
    Solid Quality Learning
    www.solidqualitylearning.com


    "David Portas" <org> wrote in message
    news:phx.gbl... 


    Itzik Guest

  9. #9

    Default Re: SQL Question: Update using correlated subquery?

    Hi David,

    I take back my phrase 'incorrect syntax'. Celko just made sure that I will
    never use this kind of phrasing again ;-)
    Whenever I see someone trying to extract a scalar value from a subquery
    without using an aggregate function, A red light immediately starts to
    flicker and an alarm sounds in my head.
    I immediately recall the many hours I spent rewriting triggers that were
    written to handle only one row inserts/updates/deletes that caused
    applications to fail when starting to use multi row inserts/updates/deletes.
    John's query WILL FAIL if the subquery returns more than 1 value.
    The Join syntax WILL WORK although you can't predict which of the values
    from table 2 will be chosen.
    As john did not specify any criteria for such a case, I assumed that it
    doesn't matter.
    I see the potential risk and you might claim that you want the query to fail
    in this case but from my practical point of view, If he didn't say which
    value he wants to choose, I rather take a 'random' one and not fail the
    query.

    I see and understand your (and Celko's) argument and will be more carefull
    in the future making such statements.

    I think it's an important issue and i'm happy this discussion developed this
    way.
    Thank you all very much for making this point crystal clear :-)

    HTH


    "David Portas" <org> wrote in message
    news:phx.gbl... 


    Amy Guest

Similar Threads

  1. correlated subquery problem
    By SethosLA@gmail.com in forum MySQL
    Replies: 3
    Last Post: October 9th, 07:41 PM
  2. SQL Server 2000 Correlated Query Question
    By MDW in forum ASP Database
    Replies: 2
    Last Post: June 13th, 05:13 PM
  3. Update more than one record (Subquery returned more than 1 value)
    By Mikael in forum Microsoft SQL / MS SQL Server
    Replies: 8
    Last Post: August 20th, 02:46 PM
  4. Correlated Subquery or Distinct
    By stabbert in forum IBM DB2
    Replies: 0
    Last Post: July 31st, 02:07 PM
  5. SQL subquery question
    By derek in forum Microsoft SQL / MS SQL Server
    Replies: 1
    Last Post: July 9th, 06:48 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139