0

I have 2 tables:

LOGS_TABLE:

FID           (NUMBER)
SERIAL_NUMBER (VARCHAR2)
LOG_RESULT    (VARCHAR2)

POINT_TABLE:

FID           (NUMBER)
SERIAL_NUMBER (VARCHAR2)
FID_LOG       (NUMBER)

The attribute to join tables is SERIAL_NUMBER, but what I need is to insert into POINT_TABLE.FID_LOG values from LOGS_TABLE.FID on basement SERIAL_NUMBER relations.

How can I do this?

lorond
  • 3,856
  • 2
  • 37
  • 52
Lazyman
  • 67
  • 2
  • 5
  • The question is unclear, at least to me. Can you share some sample data and the result you're trying to get for that sample? – Mureinik Aug 25 '16 at 08:18
  • Possible duplicate of [Update statement with inner join on Oracle](http://stackoverflow.com/questions/2446764/update-statement-with-inner-join-on-oracle) – lorond Aug 25 '16 at 08:41

1 Answers1

0

For Oracle you may use following statement:

UPDATE (SELECT p.FID_LOG fid_log
             , l.FID     fid
          FROM POINT_TABLE p
             , LOGS_TABLE  l
         WHERE p.SERIAL_NUMBER = l.SERIAL_NUMBER)
   SET fid_log = fid

Also it is possible to use MERGE statement like this:

 MERGE INTO POINT_TABLE p
 USING (SELECT * 
          FROM LOGS_TABLE) l
    ON (p.SERIAL_NUMBER = l.SERIAL_NUMBER)
  WHEN MATCHED THEN 
UPDATE 
   SET p.FID_LOG = l.FID;
Konstantin Konyshev
  • 1,026
  • 9
  • 18
  • I dont know why, but it shows me followinf error "ORA-00942: table or view does not exist" for LOGS TABLE.... Its interesting, because I can see LOGS TABLE in database content... – Lazyman Aug 25 '16 at 09:36
  • I have updated query. May be this is because of alias names. Also, please, try to use table name with schema prefix, like this: "schema"."table" https://docs.oracle.com/cd/B19306_01/server.102/b14200/sql_elements008.htm – Konstantin Konyshev Aug 25 '16 at 11:40