Oracle 21c

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

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;
9 rows affected

SELECT *
FROM Food;


Code
Group
Quantity
Color


Apple
Fruit
44
red

Apple
Fruit
1
yellow

Pineapple
Fruit
14
brown

Apple
Fruit
12
red

Banana
Fruit
1
yellow

Tomatoes
Vegetables
8
red

Cucumbers
Vegetables
3
green

Cigars vanille
Other
1
black

Cigars
Other
2
brown


CREATE TABLE Food1 AS 
SELECT *
FROM Food;
9 rows affected

CREATE TABLE Food2 AS 
SELECT *
FROM Food;
9 rows affected

CREATE TABLE Food3 AS 
SELECT *
FROM Food;
9 rows affected

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;
3 rows affected

SELECT *
FROM Groups


ID
Desc


A001
Fruit

A002
Vegetables

B099
Other


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

SELECT *
FROM Food1;


Code
Group
Quantity
Color


A001
Fruit
44
red

A001
Fruit
1
yellow

A001
Fruit
14
brown

A001
Fruit
12
red

A001
Fruit
1
yellow

A002
Vegetables
8
red

A002
Vegetables
3
green

B099
Other
1
black

B099
Other
2
brown


MERGE INTO Food2 A
USING (
   SELECT *
   FROM Groups
) B
ON (A."Group"=B."Desc")
WHEN MATCHED THEN UPDATE SET
A."Code"=B."ID"
WHERE A."Code" NOT IN ('Apple','Banana')
;
5 rows affected

SELECT *
FROM Food2;


Code
Group
Quantity
Color


Apple
Fruit
44
red

Apple
Fruit
1
yellow

A001
Fruit
14
brown

Apple
Fruit
12
red

Banana
Fruit
1
yellow

A002
Vegetables
8
red

A002
Vegetables
3
green

B099
Other
1
black

B099
Other
2
brown


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

SELECT *
FROM Food3;


Code
Group
Quantity
Color


A001
Fruit
44
red

A001
Fruit
1
yellow

A001
Fruit
14
brown

A001
Fruit
12
red

A001
Fruit
1
yellow

A002
Vegetables
8
red

A002
Vegetables
3
green

Cigars vanille
Other
1
black

Cigars
Other
2
brown

Code	Group	Quantity	Color
Apple	Fruit	44	red
Apple	Fruit	1	yellow
Pineapple	Fruit	14	brown
Apple	Fruit	12	red
Banana	Fruit	1	yellow
Tomatoes	Vegetables	8	red
Cucumbers	Vegetables	3	green
Cigars vanille	Other	1	black
Cigars	Other	2	brown

Code	Group	Quantity	Color
A001	Fruit	44	red
A001	Fruit	1	yellow
A001	Fruit	14	brown
A001	Fruit	12	red
A001	Fruit	1	yellow
A002	Vegetables	8	red
A002	Vegetables	3	green
B099	Other	1	black
B099	Other	2	brown