Update

MSSQL Create Update Trigger (QuickRef)

/ MSSQL Server /

A basic update trigger. This specific trigger can be used for audit purposes.

CREATE TRIGGER dbo.TriggerDesc_UPDATE
ON dbo.TableName FOR UPDATE
AS
BEGIN
  IF UPDATE(ColumnName)
  BEGIN
    INSERT INTO dbo.AuditLogTable (IDColumn, OldValue, NewValue)
    SELECT i.IDColumn, d.Value, i.Value
    FROM inserted I
    JOIN deleted d ON i.IDColumn = d.IDColumn
  END
END

MSSQL update value dependent on original

/ MSSQL Server /

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