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 |