How to UPDATE with JOIN in Oracle

Let suppose you have these Food and Groups tables and your target is to UPDATE values of the frist one using the values of the second.

CREATE TABLE Food(
"Code" varchar(50) NOT NULL,
"Group" varchar(100) NULL,
"Quantity" int NOT NULL,
"Color" varchar(50) NULL
);

INSERT ALL
   INTO Food ("Code","Group","Quantity","Color") VALUES ('Apple', 'Fruit', 44, 'red')
   INTO Food ("Code","Group","Quantity","Color") VALUES ('Apple', 'Fruit', 1, 'yellow')
   INTO Food ("Code","Group","Quantity","Color") VALUES  ('Pineapple', 'Fruit', 14, 'brown')
   INTO Food ("Code","Group","Quantity","Color") VALUES  ('Apple', 'Fruit', 12, 'red')
   INTO Food ("Code","Group","Quantity","Color") VALUES  ('Banana', 'Fruit', 1, 'yellow')
   INTO Food ("Code","Group","Quantity","Color") VALUES  ('Tomatoes', 'Vegetables', 8, 'red')
   INTO Food ("Code","Group","Quantity","Color") VALUES  ('Cucumbers', 'Vegetables', 3, 'green')
   INTO Food ("Code","Group","Quantity","Color") VALUES  ('Cigars vanille', 'Other', 1, 'black')
   INTO Food ("Code","Group","Quantity","Color") VALUES  ('Cigars', 'Other', 2, 'brown')

SELECT 1 FROM DUAL;

CREATE TABLE Groups(
	"ID" varchar(50) NOT NULL,
	"Desc" varchar(100) NOT NULL
);

INSERT ALL
   INTO Groups ("ID","Desc") VALUES ('A001', 'Fruit')
   INTO Groups ("ID","Desc") VALUES ('A002', 'Vegetables')
   INTO Groups ("ID","Desc") VALUES ('B099', 'Other')
SELECT 1 FROM DUAL;

Example 1

You can use the following query to UPDATE the values of Food using the values of Groups, without any filter conditions.

MERGE INTO Food A
USING (
   SELECT *
   FROM Groups
) B
ON (A."Group"=B."Desc")
WHEN MATCHED THEN UPDATE SET
A."Code"=B."ID";

Example 2

You can use the following query to UPDATE the values of Food using the values of Groups, applying a WHERE clause to the left table (which is Food in our example).

MERGE INTO Food A
USING (
   SELECT *
   FROM Groups
) B
ON (A."Group"=B."Desc")
WHEN MATCHED THEN UPDATE SET
A."Code"=B."ID";

Example 3

You can use the following query to UPDATE the values of Food using the values of Groups, applying a WHERE clause to the right table (which is Groups in our example).

MERGE INTO Food A
USING (
   SELECT *
   FROM Groups
   WHERE "Desc" != 'Other'
) B
ON (A."Group"=B."Desc")
WHEN MATCHED THEN UPDATE SET
A."Code"=B."ID";

You can find an interactive version of these examples following this link .