Post reply

UPDATE

  • September 24, 2009 at 9:58 am

    #1058133

    Aditya Kota (9/24/2009)

    My apology guys.

    The example provided makes the question confusing.

    Otherwhise it was a good question though.

  • September 24, 2009 at 10:01 am

    #1058134

    I almost answered No based on the example even though I use FROM in UPDATE statements all the time. I echo the earlier the suggestions that some questions are better off without a poorly written example.

  • September 24, 2009 at 10:43 am

    #1058151

    I know that the intended database environment for this question was SQL Server so the answer is yes, however if running T-SQL against an Access database, the answer is no. To me technically, the correct answer is 'depends' yes. (Corrected) I just did the research and discovered that T-SQL is SQL Server specific. Access uses JET SQL. I maintain applications that can run against either sql server or access and share 95% of the sql statements. Update queries against joined tables is one area that I have to be specific to the target database.

  • September 24, 2009 at 11:30 am

    #1058176

    Yes

    and we can use all the DML operation while updation

  • September 24, 2009 at 4:10 pm

    #1058301

    amit_adarsh (9/24/2009)

    Yes

    and we can use all the DML operation while updation

    I think you meant to say updationing.

    Tom Garth
    Vertical Solutions[/url]

    "There are three kinds of men. The one that learns by reading. The few who learn by observation. The rest of them have to pee on the electric fence for themselves." -- Will Rogers

  • September 24, 2009 at 6:50 pm

    #1058326

    Bob Hovious 24601 (9/23/2009)

    update table1

    set val=a2.val

    from table2 a2

    where table1.id = a2.id

    Hey Bob... Jeff has posted about this type of update before. Basically, it's a time bomb waiting to explode, and when it does it will cause extreme slowness in your server.

    The first table in the from clause should always be the target table. Therefore, the correct way to write this is:

    update table1

    set val=a2.val

    from table1, table2 a2

    where table1.id = a2.id

    Wayne
    Microsoft Certified Master: SQL Server 2008
    Author - SQL Server T-SQL Recipes

    If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
    Links:
    For better assistance in answering your questions
    Performance Problems
    Common date/time routines
    Understanding and Using APPLY Part 1 & Part 2

  • September 24, 2009 at 9:00 pm

    #1058335

    Its one of the tricky question trying to confuse the reader.

    I almost hit NO looking at the example (because it doesnt make sense), but come to think again, the question was asking whether its possible to have FROM in an UPDATE t-sql. Answer is YES. I told myself i would be arguing here if the answer was a NO :crazy:

    Simon Liew
    Microsoft Certified Master: SQL Server 2008

  • September 25, 2009 at 7:31 am

    #1058445

    Very easy!

  • September 25, 2009 at 8:07 am

    #1058469

    Think there are a few spelling mistakes? An an extra character?

    update table

    set a1.coulmn=a2.column<

    from table1 as a1, table2 as a2

    where a1.colum1 = a2.column2

  • September 25, 2009 at 8:45 am

    #1058500

    VM-723206 (9/25/2009)

    Very easy!

    To us maybe - but I'm tempted to send a link to the answer to some of our developers, who seem to be incapable of using this syntax.

  • September 25, 2009 at 1:20 pm

    #1058640

    Jeff has posted about this type of update before. Basically, it's a time bomb waiting to explode, and when it does it will cause extreme slowness in your server.

    Thanks for the heads up! In practice, I always use the JOIN format from my first example, so the table to be updated is always the first table. I never use the FROM table1,table2 WHERE format and I think it is being deprecated. But when looking at someone's legacy code I might not have known to check and change if you hadn't warned me. Another SQL-related death avoided. 🙂

    __________________________________________________

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

  • September 30, 2009 at 9:41 am

    #1060049

    Currently using this a lot with SQL 2k5 but on joins.So I guess there's no difference using keyword "JOIN" and joining using a comma

    What you don't know won't hurt you but what you know will make you plan to know better

Viewing 12 posts - 16 through 26 (of 26 total)

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