How to update a table with JOIN in Snowflake

Let's suppose you have the following Food table.

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

INSERT INTO Food
  (Code, Grp, 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');

and you have a Groups as well.

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

INSERT INTO Groups
  (ID, Desc)
VALUES
  ('A001', 'Fruit'),
  ('A002', 'Vegetables'),
  ('B099', 'Other');

and you want to use the second to update the Grp column of the first, only where Quantity is more or equal to 3.

You can use the following.

UPDATE Food A
SET A.Grp=B.ID
FROM Groups B
WHERE A.Grp=B.Desc
AND A.Quantity >= 3;