Post reply

Interesting ! Update query with alias ?

  • July 29, 2004 at 6:36 am

    #108757

    Hi,

    I have a below Oracle query :

    UPDATE test1 a SET a.sno = 3

    I need the equivalent SQL Server query for the above along with with alias name 'a' set for the table test1. Please advise.

    Thanks,

    Sam

  • July 29, 2004 at 6:48 am

    #516758

    Can't do it.

    SQL Server does not allow an alias in that position of the SET command.

    You can double check me. In BOL, use the Index tab, enter Update, Update (described). Scroll down to column_name. Read the portion starting with "A table alias..."

    -SQLBill

    BOL=Books OnLine=Microsoft SQL Server's HELP

    Installed as part of the Client Tools

    Found at Start>Programs>Microsoft SQL Server>Books OnLine

  • July 29, 2004 at 6:54 am

    #516763

    How bout...

    UPDATE a SET a.sno = 3

      FROM test1 a



    Good Hunting!

    AJ Ahrens


    webmaster@kritter.net

  • July 29, 2004 at 6:57 am

    #516765

    Thanks a lot !

    It worked.

    Thanks,

    Sam

  • July 29, 2004 at 7:04 am

    #516770

    Interesting AJ! If you read the BOL reference I posted, it specifically states and shows by example that the syntax won't work.

    Guess that's one of the 'flukes' in SQL Server.

    -SQLBill

  • July 29, 2004 at 4:01 pm

    #516886

    I had to find SOMETHING to get you on You are always a great sounding board and I love your responses. Glad I could help this time



    Good Hunting!

    AJ Ahrens


    webmaster@kritter.net

  • July 30, 2004 at 3:12 am

    #516931

    LOL, so it sometimes pays not to read BOL too carefully. I missed this part somehow, and I'm using the syntax mentioned by AJ quite often and for a long time... When I needed it for the first time, I simply tested various combinations until the query worked, instead of checking BOL :-).

    But, in fact, the example BOL refers to really does not work. They just forgot to mention, that there is a workaround which works fine. The rule is, that if you want to use alias in the SET column name, you must use the same alias in the UPDATE clause (UPDATE a SET a.column... is fine, while UPDATE table SET t.column... is not - of course, both supposing that the alias is defined in the FROM clause).

    cheers, Vladan

  • July 30, 2004 at 6:45 am

    #516978

    Thanks to both of you (AJ and Vladan) for pointing out to me that if you use the alias in the UPDATE you can use it in the SET.

    Do we call that a bug or an undocumented syntax?

    Learned something new!

    -SQLBill

Viewing 8 posts - 1 through 7 (of 7 total)

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