Insert from database link

[lhg1]

Hi

I want to update a row from a nother table throw a database link.

I should be rather simple but i can't seem to get it to work.

 INSERT INTO konto a
( NDNAME )
SELECT control_name
  FROM address_name_link@p01ol1_link.world b
where a.KONTONR = b.ban

/lhg

 

[Dagon]

Are you getting an error message ?

 

[lhg1]

Hi

Yes

ORA-00904: "A"."KONTONR": invalid identifier

 

[Dagon]

That's because you are referencing the table you are inserting into (A) in the where clause of the select. That is not permitted (and has nothing to do with database links). If you need data from KONTO, you'll need to join to it in the select.

 

[SantaMufasa]

So, lhg, the method to construct your update could be:

INSERT INTO konto a1
( NDNAME )
SELECT control_name
  FROM address_name_link@p01ol1_link.world b
      ,konto a2
 where a2.KONTONR = b.ban;

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[lhg1]

Hi

I've tryede SantaMufasa methods

INSERT INTO konto a1
(NDNAME) 
SELECT control_name
  FROM address_name_link@p01ol1_link.world b
      ,konto a2
 where a2.KONTONR = b.ban;

ORA-00904: "NDNAME": invalid identifier

and with VALUES

INSERT INTO konto a1
(NDNAME) VALUES 
SELECT control_name
  FROM address_name_link@p01ol1_link.world b
      ,konto a2
 where a2.KONTONR = b.ban;

ORA-00936: missing expression

 

[SantaMufasa]

lhg1,

Could you please post the results of this command (connected as the owner of the table "KONTO")?:

describe konto

(I'm betting that NDNAME is not part of KONTO's table structure.)

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[lhg1]

Hi

Belive me I've checkede



SQL> desc konto
Name Null? Type
----------------------------------------- -------- ----------------------------
KONTONR NOT NULL VARCHAR2(9)
KUNDENAVN VARCHAR2(50)
ACCESSTYPE VARCHAR2(50)
KUNTEKST NUMBER
GRUPPE NUMBER
GRUPPESTI VARCHAR2(50)
AFMELDINGSDATO DATE
NDNAVN VARCHAR2(100)

SQL>


This is on an ORACLE 9.2 database, and I prefer to use TOAD, this desc is done in sqlplus.

 

[SantaMufasa]

ORA-00904: "NDNAME": invalid identifier

Belive me I've checked

[tt]SQL> desc konto
Name Null? Type
----------------------------------------- -------- -------------------
KONTONR NOT NULL VARCHAR2(9)
KUNDENAVN VARCHAR2(50)
ACCESSTYPE VARCHAR2(50)
KUNTEKST NUMBER
GRUPPE NUMBER
GRUPPESTI VARCHAR2(50)
AFMELDINGSDATO DATE
NDNAVN VARCHAR2(100)[/tt]

So, where in the above DESCRIBE do you see the missing column name, "NDNAME"?


Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[lhg1]

Hi


Crap - yes i see it....now


Stil seems to have a bit of a problem with the SQL (after writing the rigth name column)

ORA-01400: cannot insert NULL into("SLA_OWNER"."KONTO"."KONTONR")

 

[SantaMufasa]

Your INSERT statement directs Oracle to create new rows in the KONTO table, but you are placing a value in only the "NDNAVN" (I'm guessing) column. If you are placing a value in that one column only, then Oracle attempts to place NULL into all of the other columns.

Your DESCRIBE statement confirms that the "KONTONR" column cannot ever contain a NULL, which causes your error.

Therefore, you must either:

1) Remove the NOT NULL constraint from the "KONTONR" column, or
2) Supply a value in the "KONTONR" column for each new that you are INSERTing.

Let us know your thoughts.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[lhg1]

Hi

What I wantede to do was to update the NDNAVN on konto from control_name from the database link.

And only on the existing rows in konto, not create new rows in konto.

The table konto is a small part of the address_name_link@p01ol1_link.world, and should remain that way, I just wantede to put the corresponding control_name into NDNAVN

/Lhg

 

[Dagon]

You need to look at UPDATE rather than INSERT, then. At a guess, something like:

UPDATE konto a1
SET NDNAME =
(SELECT control_name
  FROM address_name_link@p01ol1_link.world b
 where a1.KONTONR = b.ban);

 

[lhg1]

Hi

When using this update I can't figure out how to update all the rows, I get this error.

ORA-01427: single-row subquery returns more then one row

 

[Dagon]

That is because the condition retrieves more than one row and Oracle doesn't know which one to use. If all the rows have the same value in NDNAME but you are just getting more than one of them, you could add a DISTINCT to stop the error:

UPDATE konto a1
SET NDNAME =
(SELECT distinct b.control_name
  FROM address_name_link@p01ol1_link.world b
 where a1.KONTONR = b.ban);

If there is more than one DISTINCT value, you need to review the joining conditions to ensure you have enough to produce a unique match.

 

[lhg1]

The NDNAVN is not DISTINCT.

Well the a1.KONTONR and b.ban is a unique match.

I've been playing with using the join command, but how to get this to update is beyond me.

/Lhg

 

[Dagon]

It is plainly not unique or you would not be getting this error. Look for the duplicates using a query such as:

select * from address_name_link@p01ol1_link.world 
where kontonr in
(select kontonr
from
(SELECT distinct a1.kontonr, b.control_name  
FROM address_name_link@p01ol1_link.world b, konto a1
where a1.KONTONR = b.ban)
group by a1.kontonr
having count(*) > 1)