UPDATE Statement with Alias - Sybase SQL Anywhere to SQL Server Migration

Both Sybase ASA 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.

UPDATE with Alias

Sybase ASA allows you optionally defining an alias for the updated table:

Sybase SQL Anywhere:

  -- UPDATE with an alias
  UPDATE specs AS 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 [/sybase-asa-to-sql-server|Sybase SQL Anywhere to SQL Server Migration]].