MSSQL update value dependent on original

Query to change the format of a postal code string for specific postal codes. This specific script changes all values in the postcode column from format  ‘1234AB’ to ‘1234 AB’.

UPDATE tablename
SET postcode = LEFT(LTRIM(postcode),4) + ' ' + RIGHT(RTRIM(postcode),2)
WHERE
  LEN(LTRIM(postcode)) = 6 AND
  LEN(REPLACE(postcode, ' ', '')) = 6 AND
  ((land = 'Nederland') OR (land = 'Nederland (NL)'))

Other example to replace values. This time replacing a value with a string from another table.

UPDATE   a
SET      ColumnX = b.ColumnY
FROM     Table1 a
JOIN     Table2 b ON b.ID=a.ID