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,

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,
ThanksIf 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.|||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.