update table1 comparing wiith table2 1

[nubees]

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,
Thanks

 

[PHV]

You have to deal with 2 queries: an update query and an append query.
UPDATE A1 INNER A2 ON A1.Server = A2.name
SET A1.ttyport = A2.ttyport;

INSERT INTO A1 (Server, ttyport)
SELECT A2.name, A2.ttyport FROM A2
WHERE Not Exists (SELECT * FROM A1 WHERE A1.Server = A2.name);

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[nubees]

PHV,

Thank you very very much for the reply. It worked like a charm.
However, i have a problem with update query. It worked with one table and is not working with other.

In table1 there are 781 rows and I want to update table1 with some data of table2(1141 rows).
When I run the query it doenot return any error but says "Do you want to update 4228 rows?"

It really surprises me because there are only 781 rows in table1 and it should update only 781 rows no matter what. I dont know how the query is arrivng to that figure "4228".

Is that figure just misleading me or is it something serious I should be looking deep into?

When I click yes and check table1, there are still 781 rows and the data is updated. I didnot check whether the updated data is correct/not yet.

UPDATE sqlInBaseStruc14 INNER JOIN location28 ON sqlInBaseStruc14.[Logical Name], location28.name SET sqlInBaseStruc14.Cabinet = location28.cabinet, sqlInBaseStruc14.Facility = location28.facility, sqlInBaseStruc14.[Grid-Aaa] = location28.row_num, sqlInBaseStruc14.[Grid-999] = location28.column_num;

Do you see any error in the above query? could you please shed some light on my problem?

Thanks,
nubee

 

[nubees]

oops, sorry,

the above query should be like this

UPDATE sqlInBaseStruc14 INNER JOIN location28 ON sqlInBaseStruc14.[Logical Name] = location28.name SET sqlInBaseStruc14.Cabinet = location28.cabinet, sqlInBaseStruc14.Facility = location28.facility, sqlInBaseStruc14.[Grid-Aaa] = location28.row_num, sqlInBaseStruc14.[Grid-999] = location28.column_num;

 

[PHV]

ON sqlInBaseStruc14.[Logical Name] [highlight]=[/highlight] location28.name

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[nubees]

I mistyped , instead of = in my previous post. The query which I am running has ON sqlInBaseStruc14.[Logical Name] = location28.name

 

[PHV]

You have only one location28.name record for one sqlInBaseStruc14.[Logical Name] ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[nubees]

You have only one location28.name record for one sqlInBaseStruc14.[Logical Name] ?

no, actually I have more than one records for eg:

table: location

name cabinet facility row_num
12 slot smartjack 94 A1 BZ
12 slot smartjack 93 A1 BY
12 slot smartjack 93 A1 BY
12 slot smartjack 201 A1 BS
(empty) 102 A1 BY
(empty) 421 A1 CM
(empty) 162 A1 CN
207 A1 AX
207 A1 AX
323 A1 BH


table: sqlInBaseStruc
structure: I have about 10 columns in this table with cabinet,facility,grid-Aaa blank. I want to insert the data of location to this table.
Logical Name Cabinet Facility Grid-Aaa ... ...
12 slot smartjack
12 slot smartjack
12 slot smartjack
12 slot smartjack
(empty)
(empty)
(empty)



The Logical Name field of sqlInBaseStruc has all the data of location.name + other Logical Names.

Am I making any sense? Do I have to use different kind of query for this? an outer join may be?