Update oracle with different information

0

I'm trying to do an oracle update with completely different information and I do not know how to do it. I want all other campuses to be the same as campus 1, where this id_campu is a foreign key with another table. Example:

col1.id | col2.descricao |col3.id_campus| col4.flag 1 desc1 1 a 2 desc2 1 b 3 desc3 1 c 4 desc2 1 d 5 desc3 1 e 6 1desc 2 a 7 1desc 2 b 8 1desc 2 c 9 desc2 2 b 10 desc1 2 c 11 1desc 3 b 12 d1esc 3 d 13 d1esc 3 c 14 desc2 3 e 15 des1c 5 a

UPDATE table1 t1
   SET (descricao, flag) = 
       (SELECT t2.descricao, t2.flag
          FROM table1 t2
         WHERE t1.id_campus = 1
       )
 WHERE t1.id_campus in (select t3.id_campus from table1 t3 where t3.id_campus not in (1));

But there, it gives the error: ORA-01427: the subquery of a single linharetorna more than one line.

    
asked by anonymous 10.08.2017 / 00:55

1 answer

0

It seems that in the select below, it is returning more than one id_campus = 1 ... Believing that in this "table1" it is foreign to a "campus" table, for example.

  

(SELECT t2.description, t2.flag             FROM table1 t2            WHERE t1.id_campus = 1          )

Based on all campuses need to have the description and flag equal to campus 1, could do:

UPDATE table1
SET DESCRICAO = ( SELECT DESCRICAO FROM TABLE1 WHERE ID_CAMPUS = 1 )
    , FLAG = ( SELECT FLAG FROM TABLE1 WHERE ID_CAMPUS = 1 )
WHERE ID_CAMPUS <> 1

But remember to make sure that in this table1 you only have a pro-campus 1 record, otherwise it may be a base error, or it may have a valid date for those features ...

    
01.09.2017 / 18:46