Results 1 to 8 of 8
  1. #1
    Join Date
    Mar 2004
    Posts
    205

    Unanswered: Update query syntax ?

    Is there anything like below in SQL Server 7.0 ?

    update (table1 inner join table2 on table1.sno = table2.sno1) set .....

    Please advice.

    Thanks,
    Sam

  2. #2
    Join Date
    Feb 2004
    Posts
    492
    like I said in my previous reply:
    The update doesn't take the first ( , it needs a tablename or view.

    try something like below:

    update table1
    set myint1 = t2.myref
    from table1 t1
    inner join table2 t2 on (t1.myref = t2.myref)
    where t2.myval = somevalue

    You can't update multiple tables

  3. #3
    Join Date
    Mar 2004
    Posts
    205
    Hi,

    Actually I too tried this query in SQL Server 7.0, its not working. But my client says that it is working and rows got updated, Actually he says that this 'join type table source' can be given next to 'UPDATE' clause.

    I am at confusion. How it is working for him ?

    Please advice.

    Thanks,
    Sam

  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    You may be able to accomplish this if
    "table1 inner join table2 on table1.sno = table2.sno1"
    is saved as a view, and then issue your update statement against the view. But MSSQL Syntax does not allow what your client is describing.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  5. #5
    Join Date
    Feb 2004
    Posts
    492
    blindman; that won't work either. The same goes for insert-statements. It's just impossible to make modifications to multiple-tables in one update/insert-statement. We have procedures to handle these things. Just think of what could happen.

  6. #6
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Maybe your client means this?


    update t1 set...
    from table1 t1
    inner join (select ... from table1 inner join table2 on table1.sno = table2.sno1) i1
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  7. #7
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Kaiowas; views can be updatable, though I don't do this myself, nor do I recommend it because I think it is sloppy.

    "Updatable views can modify more than one table involved in the view. The DELETE, INSERT, and UPDATE statements can reference a view as long as SQL Server can translate the user's update request unambiguously to updates in the base tables referenced in the view's definition. "

    ...though this is SQL Server 2000 behavior. I'm not sure about this guy's 7.0 installation.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  8. #8
    Join Date
    Feb 2004
    Posts
    492
    Quote Originally Posted by blindman
    Kaiowas; views can be updatable, though I don't do this myself, nor do I recommend it because I think it is sloppy.

    "Updatable views can modify more than one table involved in the view. The DELETE, INSERT, and UPDATE statements can reference a view as long as SQL Server can translate the user's update request unambiguously to updates in the base tables referenced in the view's definition. "
    you're right, it only works on partitioned views! It's not a regular view by far tho. I guess I was blind on 'ol 7

Posting Permissions

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