SQL: Update a table with Common Table Expression (CTE)

sql-cte-update.sql

/*
  Example of how to update a table in SQL using a Common Table Expression (CTE)
*/

USE [SomeTable]
GO

;WITH cte AS (
	SELECT te.LandEntityId, te.BirthDate, te.BirthDateId,  dd.DateId
	FROM TypedEntity te
	LEFT JOIN [SomeOtherTable].[dbo].[DimDate] dd
	ON dd.Date = te.BirthDate
)
UPDATE cte
SET cte.BirthdateId = cte.DateId;

/* Another Example: Prepend a zero to a column if the length is less than 2 characters */
USE [SomeTable]
GO

;WITH cte AS (
	SELECT SomeColumn
	FROM TypedEntity te
	WHERE LEN(SomeColumn) < 2
)
UPDATE cte
SET cte.SomeColumn = CONCAT('0', cte.SomeColumn);