Results 1 to 3 of 3
  1. #1
    Join Date
    Oct 2004
    Posts
    10

    Unanswered: help with update query

    Hello all,

    I have a database with table "A1" of fields
    1. Id (Auto number)
    2. Server(this matches the name field of A2)
    3.Vendor
    4.Model
    5.ttyport
    This table has 200 rows in it

    and 2nd table A2 of fields

    1.name(this matches the server field of A1)
    2.ttyport
    This has 100 rows in it.

    I want to accomplish something like this,
    Code:
    Update A1 set A1.ttyport=A2.ttyport,A1.ttyport=A2.ttyport, where A1.Server=A2.name
    
    if A1.Server IS NOT EQUAL A2.name then create another row in A1 with A2.name, A2.ttyport and leaving the rest of the fields with "null"
    is it possible? can some one help me with the modification of the query ?

    please help,
    Thanks

  2. #2
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    If you want to do it using only one query, I'd say you can't do that - as far as I know, there's no way to do UPDATE and INSERT in the same statement.

    You'll have to do the UPDATE with the one you already have (why do you have double "A1.ttyport=A2.ttyport" in it?) and write another one to INSERT data into A1 table.

  3. #3
    Join Date
    Dec 2002
    Posts
    134
    First update, than insert

    update A1
    set ttyport = (
    select A2.ttyport
    from A2
    where A1.Server=A2.name
    )
    where exists (
    select A2.ttyport
    from A2
    where A1.Server=A2.name
    )

    You can ommit where clause from update if you know that every record in A1 has 1 record in A2.

    insert into A1 (servername, ttyport)
    select name, ttyport
    from A2
    where not exists (
    select A2.ttyport
    from A2
    where A1.Server=A2.name
    )


    I do not know what db and version you are on, but DB2 and Oracle have MERGE command which allows to do both operations in a single SQL.

Posting Permissions

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