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,
is it possible? can some one help me with the modification of the query ?

please help,
Thanks

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.

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.