Update oracle with separate information

Asked

Viewed 550 times

0

I’m trying to do an oracle update with completely different information and I don’t know how to do it. I want all other campuses to be the same as campus 1, where this id_campus 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));

Only there, it gives the error: ORA-01427: the sub-consumption of a single line.

sql oracle

  • Improve the example by describing the tables, confessed not have understood this "id_campus = 1" and "id_campus = 1" in principle a plsql block with a cursor should solve the question.

  • As @Motta quoted for the situation you mentioned, you will need a plsql block using a select(cursor) in a FOR passing these distinct parameters to the update. LINK plsql to better understand

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 of a table "campus", for example.

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

Based on all campuses need to have 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 ensure that this table1 has only a pro campus 1 record, otherwise it may be a base error, or maybe it has a valid date of these features...

Browser other questions tagged sql oracle

You are not signed in. Login or sign up in order to post.