Update query with multiple tables

CREATE TABLE TEMP_MAIN (EID VARCHAR2 (10) PRIMARY KEY, ENAME VARCHAR2(30), ADDRESS VARCHAR2(100));

CREATE TABLE TEMP_SUB (EID VARCHAR2 (10) PRIMARY KEY, ENAME VARCHAR2(30), ADDRESS VARCHAR2(100));

Temp_Main

INSERT INTO TEMP_MAIN (EID, ENAME, ADDRESS) VALUES ('1220', 'Ravi','-')

INSERT INTO TEMP_MAIN (EID, ENAME, ADDRESS) VALUES ('1221', 'Rahul''-')

INSERT INTO TEMP_MAIN (EID, ENAME, ADDRESS) VALUES ('1223', 'Surendra', 'NA')

Temp_Sub

INSERT INTO TEMP_SUB (EID, ADDRESS) VALUES ('1220', 'Bangalore')

INSERT INTO TEMP_SUB (EID, ADDRESS) VALUES ('1221', 'Hyderabad')

INSERT INTO TEMP_SUB (EID, ADDRESS) VALUES ('1230', 'Chennai')

Temp_Main
	EID	ENAME	Address
	1220	Ravi	-
	1221	Rahul	-
	1223	Surendra	NA
Temp_SUB
	EID	ENAME	Address
	1220	Ravi Sankar	Bangalore
	1221	Rahul Raj	Hyderabad
	1230	Surendra Pokala	Chennai

Execute below query: It updates the address in Temp_Main with the address in Temp_SUB

UPDATE temp_main SET address = ( SELECT temp_sub.address FROM temp_sub WHERE temp_sub.eid=temp_main.eid) WHERE EXISTS

( SELECT temp_sub.address FROM temp_sub WHERE temp_sub.eid = temp_main.eid);

Temp_Main
	EID	ENAME	Address
	1220	Ravi	Bangalore
	1221	Rahul	Hyderabad
	1223	Surendra	NA

Execute below query to update multiple columns.

UPDATE temp_main SET address = ( SELECT temp_sub.address FROM temp_sub WHERE temp_sub.eid=temp_main.eid) ,ename=( SELECT temp_sub.ename FROM temp_sub WHERE temp_sub.eid=temp_main.eid)

WHERE EXISTS

( SELECT temp_sub.address,temp_sub.ename

FROM temp_sub

WHERE temp_sub.eid = temp_main.eid);

Temp_Main
	EID	ENAME	Address
	1220	Ravi Sankar	Bangalore
	1221	Rahul Raj	Hyderabad
	1223	Surendra	NA

Update query with a single table but with multiple conditions (CASE)

Below query updates AGE column based on the value in ADDRESS column.

Note: WHERE is required at last, otherwise all the column's data which is not mentioned in CASE-WHEN will be updated with <null> value.

UPDATE temp_main

SET AGE=20,temp_main.address = CASE

WHEN temp_main.address = 'BLR' THEN 'Bangalore'

WHEN temp_main.address = 'XYZ' THEN 'Hyderabad'

END WHERE temp_main.address IN('BLR','XYZ');