Oracle 18c

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE CLIENTS (
	ID          NUMBER(10),
	NAME        VARCHAR(30) NOT NULL,
	SURNAME     VARCHAR(30) NOT NULL,
	CBUDGET     NUMBER(10, 2),
	COMPANYID   NUMBER(10) NOT NULL,
	PRIMARY KEY ( ID )
);
CREATE TABLE CONTRACTS (
	ID              NUMBER(20),
	CONTRACTVALUE   CHAR(255) NOT NULL,
	COMPANYID       NUMBER(10) NOT NULL,
	CLIENTID        NUMBER(10) NOT NULL,
	TRANSACTIONID   NUMBER(10) NOT NULL,
	PRIMARY KEY ( ID )
);
CREATE TABLE "Transaction" (
	ID         NUMBER(10),
	TYPE       NUMBER(1) NOT NULL,
	PRICE      NUMBER(10, 2),
	TDATE      DATE,
	CLIENTID   NUMBER(10) NOT NULL,
	PRIMARY KEY ( ID )
);
INSERT INTO Clients(ID,name,surname,Cbudget,CompanyID) VALUES (1,'hamza','bacara',98372.200,2);
1 rows affected

INSERT INTO Clients(ID,name,surname,Cbudget,CompanyID) VALUES (2,' ','bacara',87432.400,1);
1 rows affected

INSERT INTO Clients(ID,name,surname,Cbudget,CompanyID) VALUES (3,'batikan','falay',213132.00,2);
1 rows affected

INSERT INTO "Transaction"(ID,Type,Price,TDATE,ClientID)VALUES(1,'1',5000,current_date,2);
1 rows affected

INSERT INTO "Transaction"(ID,Type,Price,TDATE,ClientID)VALUES(2,'1',6000,TO_DATE('11/09/2006','DD/MM/YYYY'),1);
1 rows affected

INSERT INTO "Transaction"(ID,Type,Price,TDATE,ClientID)VALUES(3,'2',9000,current_date,3);
1 rows affected

INSERT INTO Contracts(ID,ContractValue,CompanyID,ClientID,TransactionID)VALUES(1,'1 Million $',1,2,1);
1 rows affected

INSERT INTO Contracts(ID,ContractValue,CompanyID,ClientID,TransactionID)VALUES(2,'50 Million $',2,1,2);
1 rows affected

INSERT INTO Contracts(ID,ContractValue,CompanyID,ClientID,TransactionID)VALUES(3,'100 Million $',2,2,3);
1 rows affected

SELECT * FROM CLIENTS


ID
NAME
SURNAME
CBUDGET
COMPANYID


1
hamza
bacara
98372.2
2

2
 
bacara
87432.4
1

3
batikan
falay
213132
2


SELECT * FROM CONTRACTS


ID
CONTRACTVALUE
COMPANYID
CLIENTID
TRANSACTIONID


1
1 Million $
1
2
1

2
50 Million $
2
1
2

3
100 Million $
2
2
3


SELECT * FROM "Transaction"


ID
TYPE
PRICE
TDATE
CLIENTID


1
1
5000
03-DEC-20
2

2
1
6000
11-SEP-06
1

3
2
9000
03-DEC-20
3


SELECT * FROM Contracts WHERE ClientID IN 
(SELECT T.ClientID FROM "Transaction" T 
INNER JOIN Contracts CT ON CT.TransactionID = T.id WHERE CT.ContractValue = '1 Million $');


ID
CONTRACTVALUE
COMPANYID
CLIENTID
TRANSACTIONID


1
1 Million $
1
2
1

3
100 Million $
2
2
3


-- This change is needed
UPDATE CONTRACTS 
SET CLIENTID = 3 
WHERE ID = 3
1 rows affected

-- New data in contracts table
SELECT * FROM CONTRACTS


ID
CONTRACTVALUE
COMPANYID
CLIENTID
TRANSACTIONID


1
1 Million $
1
2
1

2
50 Million $
2
1
2

3
100 Million $
2
3
3


-- Again executing the query
SELECT * FROM Contracts WHERE ClientID IN 
(SELECT T.ClientID FROM "Transaction" T 
INNER JOIN Contracts CT ON CT.TransactionID = T.id WHERE CT.ContractValue = '1 Million $');


ID
CONTRACTVALUE
COMPANYID
CLIENTID
TRANSACTIONID


1
1 Million $
1
2
1