How to UPDATE a table with JOIN

Example 1

Let's suppose we have a #Food table and we want to UPDATE the rows where Quantity is greather or equal then 3, using another table named #Groups.

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');
                            

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');

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

You can find an interactive version of this example following this link .