How to update a table whose primary key is the primary key in another table?

Question

I have these two tables :

CREATE TABLE COPIE(IDC VARCHAR2(10) PRIMARY KEY,
                   ETAT CHAR(1)
                     CHECK (ETAT IN ('M','P','D')),
                   IDO VARCHAR2(10) REFERENCES OUVRAGE(IDO));

CREATE TABLE PRET(IDC VARCHAR2(10) REFERENCES COPIE(IDC),
                  IDE REFERENCES ETUDIANT(IDE),
                  DATEP DATE,
                  DATER DATE,
                  AVIS VARCHAR2(7)
                    CHECK (AVIS IN ('LIKE','DISLIKE')),
                  IDB VARCHAR2(10) REFERENCES BIBLIO(IDB),
                  PRIMARY KEY(IDC,IDE,DATEP));

so basically im trying to make a procedure which updates ETAT in TABLE COPIE WHERE its datep is not null and dater is null ( in table pret )

Answer 1

As far as I understood your problem, you can use merge as following:

Merge into copie c
Using (select * from pret
       Where dater is null and datep is not null) t
On (c.idc = t.idc)
When matched then 
Update set c.etat = <value whichever you want>

Or you can directly use update statement as

Update copie c 
Set c.etat = <value whichever you want>
Where c.idc in 
   (select t.idc
      From pret t
     Where dater is null 
       and datep is not null)

Cheers!!