By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE #Food(
[Code] [nvarchar](50) NOT NULL,
[Group] [nvarchar](100) NULL,
[Quantity] [int] NOT NULL,
[Color] [nvarchar](50) NULL,
) ON [PRIMARY];
INSERT INTO #Food
( [Code], [Group], [Quantity],[Color] )
VALUES
('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');
7 rows affected
SELECT [Code], [Group], [Quantity],[Color]
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 |
CREATE TABLE #Groups(
[ID] [nvarchar](50) NOT NULL,
[Desc] [nvarchar](100) NOT NULL
) ON [PRIMARY];
INSERT INTO #Groups
( [ID], [Desc] )
VALUES
('A001', 'Fruit'),
('A002', 'Vegetables'),
('B099', 'Other');
3 rows affected
SELECT *
FROM #Groups
| ID | Desc |
|---|---|
| A001 | Fruit |
| A002 | Vegetables |
| B099 | Other |
UPDATE A
SET A.[Group]=B.ID
FROM #Food AS A
INNER JOIN #Groups AS B
ON A.[Group]=B.[Desc]
WHERE A.Quantity >= 3
5 rows affected
SELECT *
FROM #Food
| Code | Group | Quantity | Color |
|---|---|---|---|
| Apple | A001 | 44 | red |
| Apple | Fruit | 1 | yellow |
| Pineapple | A001 | 14 | brown |
| Apple | A001 | 12 | red |
| Banana | Fruit | 1 | yellow |
| Tomatoes | A002 | 8 | red |
| Cucumbers | A002 | 3 | green |