UPDATE

  • Aditya Kota

    Ten Centuries

    Points: 1392

    Comments posted to this topic are about the item UPDATE

  • The Dixie Flatline

    SSC Guru

    Points: 53253

    I thought this was a good question because I've run across a number of people who didn't realize it was possible, but the example troubles me. It just won't work. I almost answered no, thinking it was a trick question. The example really needs to be cleaned up.

    update table

    set a1.coulmn=a2.column<

    from table1 as a1, table2 as a2

    where a1.colum1 = a2.column2

    First, just saying UPDATE TABLE will throw an error. It should say UPDATE

    , unless it is a typo. I am assuming it's a typo, just like the typos in spelling "column" and the extra < at the end of one line. If not, it reads as if there were three tables: "table", "table1" and "table2"... which won't work either.

    We are supposed to be updating the table specified in the first line, but instead the code tries to set a value of a column that was aliased in the FROM (TABLE1 as A1). I've never seen that format so I cleaned it up at little and tested it. It throws the following error:

    Msg 4104, Level 16, State 1, Line 33

    The multi-part identifier "a1.val" could not be bound.

    The only columns which can be updated are those belonging to the table which is the subject of the UPDATE. That table's name is specified immediately after the word UPDATE, and not necessarily after the FROM. Since only one target table can be the target, the names of the target columns do not need to be qualified, although the source column should be.

    Two correct formats for using FROM with an UPDATE are found in the examples below.

    ;with cte (id, val) as (

    select 1,'A' union all

    select 2,'B' union all

    select 3,'C'

    )

    select *

    into [table1]

    from cte

    ;with cte (id, val) as (

    select 1,'X' union all

    select 2,'Y' union all

    select 3,'Z'

    )

    select *

    into [table2]

    from cte

    select 'Table1',* from [table1]

    select 'Table2',* from [table2]

    select 'NOW THE UPDATE HAPPENS' as [GASP!!!]

    ------------------------------------------------------------------

    ----------- proper format for using a FROM clause in an update

    ------------------------------------------------------------------

    update table1

    set val=a2.val

    from table1 a1

    join table2 a2 on a1.id = a2.id

    ----------- (yes, i used a join instead of tablelist/where)

    ------------------------------------------------------------------

    select 'Table1',* from [table1]

    drop table table1

    drop table table2

    Alternatively this will work as well

    update table1

    set val=a2.val

    from table2 a2

    where table1.id = a2.id

    __________________________________________________

    Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
    Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

  • dmoldovan

    SSCertifiable

    Points: 6334

    I extensively used this way of updating while working on SQL 2000. Now I prefer to use a CTE or, if dealing with SQL 2008, a MERGE statement.

  • geervani

    SSC Enthusiast

    Points: 146

    The answer for the question is YES. FROM clause can be used in UPDATE query. But the example query given is wrong.

    something like this will work

    update table1

    set coulmn=a2.column

    from table1 as a1, table2 as a2

    where a1.colum1 = a2.column2

  • victor.olufowobi

    SSC Enthusiast

    Points: 191

    As geervani indicated you can definitely use FROM clause in UPDATE but not in the example given.

    I would do:

    update a1

    Set a1.column= a2.column

    From table as a1 inner join table2 as a2

    On a1.column1=a2.column2

    I want my point back 😀

    Victor

  • beck.patrick

    Ten Centuries

    Points: 1167

    You can also use the FROM in SELECT, INSERTS, DELETE statements, because it is just another way to join tables.

    So the (corrected) example code

    update

    a1

    set

    col = a2.col

    from

    table1 as a1,

    table2 as a2

    where

    a1.col = a2.col

    will execute the same operation as

    update

    a1

    set

    col = a2.col

    from

    table1 as a1

    inner join table2 as a2

    on a1.col = a2.col

    When running operations with a big amount of rows, the second should have the better performance.

    [EDIT]

    This is because statement no.1 will first select all rows on both tables and then matches them with the where-clause. Statement no.2 will first match the tables with the join-clause and then select the matching rows. The performance will increase when the clause uses an index (i.e. primary key).

    [/EDIT]

  • dave.farmer

    SSCrazy

    Points: 2431

    beck.patrick (9/24/2009)


    So the (corrected) example code

    update

    a1

    set

    col = a2.col

    from

    table1 as a1,

    table2 as a2

    where

    a1.col = a2.col

    This will update table1.col to with the value of table2.col when they are already the same... the obvious conclusion is that the columns in the original question (coulmn, colum1, column and column2) are not typos! 😀

    Actually, I've worked in places where that would be entirely believable! :w00t:

  • Bhavesh-1094084

    SSC Eights!

    Points: 865

    geervani (9/24/2009)


    The answer for the question is YES. FROM clause can be used in UPDATE query. But the example query given is wrong.

    something like this will work

    update table1

    set coulmn=a2.column

    from table1 as a1, table2 as a2

    where a1.colum1 = a2.column2

    Agree.

    Alternatively you can also use

    update table1

    set somecolumn=a2.column

    from table2 as a2

    where column1 = a2.column2

    point here is if you dont specify alias for table to be updated in the from clause you can use it as table name reference. However, if you do specify alias for table1 in table from clause then you must reference the table with alias.

    geervani: In your statement just to keep t-sql cleaner I would use following

    update a1

    set a1.somecolumn=a2.column

    from table1 as a1, table2 as a2

    where a1.column1 = a2.column2

  • Cliff Jones

    SSChampion

    Points: 10517

    geervani (9/24/2009)


    The answer for the question is YES. FROM clause can be used in UPDATE query. But the example query given is wrong.

    something like this will work

    update table1

    set coulmn=a2.column

    from table1 as a1, table2 as a2

    where a1.colum1 = a2.column2

    Yes, I wasn't sure which way to answer since the syntax wasn't quite right.

  • OCTom

    SSChampion

    Points: 11755

    A suggestion for future questions. Maybe not all questions need sample code. A simple question such as "Can you use the FROM clause in a T-SQL update statement?" would have been sufficient.

    I answered yes and never looked at the code.

    :hehe:

  • Adi Cohn-120898

    SSC-Dedicated

    Points: 33944

    Like others I didn’t know what to answer. I know that FROM clause can be used in an update statement, but I also noticed that the way that the example was written, it shouldn’t work. I decided to ignore the example and give the correct answer for the question only, but I can understand why many users wrote no because of the example.

    Adi

    --------------------------------------------------------------
    To know how to ask questions and increase the chances of getting asnwers:
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

    For better answers on performance questions, click on the following...
    http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

  • jensgc

    Mr or Mrs. 500

    Points: 554

    skjoldtc (9/24/2009)


    A suggestion for future questions. Maybe not all questions need sample code.

    Although some questions very well could be written without sample code I think it is generally a good idea to have code along with a T-SQL question like this. But - the code should be correct, unless the purpose of the question is to spot the error.

  • R Wong

    SSC Enthusiast

    Points: 122

    🙁 I answered no because the code does not work. Oh, well!

  • Aditya Kota

    Ten Centuries

    Points: 1392

    My apology guys.

    The example provided makes the question confusing.

  • macrostarrphish

    Old Hand

    Points: 353

    I too answered 'no' based upon the example...perhaps the example shouldn't have been given and just the question asked, but if you ran the given statement, it would certainly fail due to:

    update table <--

    set a1.coulmn=a2.column <-- COULMN [misspelled]

    from table1 as a1, table2 as a2

    where a1.colum1 = a2.column2 <-- COLUM [misspelled]

    Was this ever QA'd? If I was handed this script to apply to production, I wouldn't have to see it fail to kick it back to the author and tell them to clean it up...

Viewing 15 posts - 1 through 15 (of 27 total)

You must be logged in to reply to this topic. Login to reply