UPDATE Statement with Alias - Oracle to SQL Server Migration

Both Oracle and SQL Server support SQL UPDATE statement that allows you to modify the existing table rows, but there are some differences so the conversion may be required.

Oracle allows you optionally defining an alias for the updated table:

Oracle:

  -- UPDATE with an alias
  UPDATE specs s
  SET s.cnt = 0
  WHERE s.name IS NULL;
  # Ok

Aliases are not supported in SQL Server:

SQL Server:

  -- UPDATE with an alias
  UPDATE specs AS s
  SET s.cnt = 0
  WHERE s.name IS NULL;
  # Msg 156, Level 15, State 1, Line 1
  # Incorrect syntax near the keyword 'AS'.
 
  -- Without AS keyword
  UPDATE specs s
  SET s.cnt = 0
  WHERE s.name IS NULL;
  # Msg 102, Level 15, State 1, Line 1
  # Incorrect syntax near 's'.
 
  -- Without alias (removing its references in SET and WHERE clauses as well)
  UPDATE specs 
  SET cnt = 0
  WHERE name IS NULL;
  # Ok

For more information, see Oracle to SQL Server Migration.