Professional Web Applications Themes

More than one update in a statement? - Microsoft SQL / MS SQL Server

I have to update several values in several tables at once - can there be multiple update statements on the one sql statement? The reason for trying it this way is that I can change the values using find/replace in a text editor and then just run the script (it saves time rather than going to every table in turn). when i try this i get an error - whats the syntax for more than one update? is it possible? thanks...

  1. #1

    Default More than one update in a statement?

    I have to update several values in several tables at once -
    can there be multiple update statements on the one sql
    statement?

    The reason for trying it this way is that I can change the
    values using find/replace in a text editor and then just
    run the script (it saves time rather than going to every
    table in turn). when i try this i get an error - whats the
    syntax for more than one update? is it possible?

    thanks
    Jon Guest

  2. #2

    Default Re: More than one update in a statement?

    you could run this in the query yzer as is:

    use pubs
    update authors set lname = lname + '1'
    update authors set lname = substring(lname, 1, len(lname) - 1)

    L-E

    "Jon" <com> wrote in message
    news:086801c366df$8b96c580$gbl... 


    Lars-Erik Guest

  3. #3

    Default Re: More than one update in a statement?

    A single UPDATE statement can update more than one column but only in one
    table.

    Look for the UPDATE syntax in BOL.

    HTH

    "Jon" <com> wrote in message
    news:086801c366df$8b96c580$gbl... 


    Amy Guest

  4. #4

    Default Re: More than one update in a statement?

    >> whats the syntax for more than one update? is it possible? <<

    No; didn't you check the syntax in BOL before posting? An UPDATE
    statement works on one and only one base table at a time. However, you
    might have been able to set ON UPDATE CASCADE actions on the related
    tables.

    It is also possible to get fairly tricky with CASE expressions in an
    UPDATE and avoid making mulitple updates to the same table.

    There is no FROM clause in a Standaerd 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.

    --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

  5. #5

    Default Re: More than one update in a statement?

    No, TSQL has no ATOMIC.
    I'm not exactly what that means, but it might be similiar to transaction
    control

    BEGIN TRAN

    update ....
    update ...

    COMMIT TRAN

    --
    HTH
    ----------------
    Kalen Delaney
    SQL Server MVP
    www.SolidQualityLearning.com


    "Bob" <net> wrote in message
    news:#phx.gbl... 
    reference 
    now 
    and 
    > <...>
    >[/ref]


    Kalen Guest

Similar Threads

  1. update statement
    By Iman in forum MySQL
    Replies: 5
    Last Post: October 4th, 08:37 AM
  2. Error in Update Statement
    By AMARITA in forum Coldfusion - Getting Started
    Replies: 10
    Last Post: June 5th, 05:25 PM
  3. sql - statement for update - id
    By pilem17 in forum Coldfusion Database Access
    Replies: 5
    Last Post: October 3rd, 06:15 AM
  4. Generated Update Statement
    By Matt M. in forum ASP.NET Data Grid Control
    Replies: 0
    Last Post: January 30th, 04:41 PM
  5. update statement
    By John Bell in forum Microsoft SQL / MS SQL Server
    Replies: 6
    Last Post: August 9th, 08:25 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