SQL Server – Aliasing Tables In UPDATES For The Oracle Folks

If you transition from Oracle to SQL Server, something as simple as aliasing a table in an UPDATE statement might stump you (things that SQL Server developers take for granted)

Here is what I mean:

Create a temporary table (#Temp) to play with:

SELECT TOP 100 *
INTO #Temp
FROM sys.partitions;

Get the structure of #Temp

This is also a trick to view the structure of temp tables. 😉

EXEC TempDB..sp_Help '#Temp'

(Could have done this instead – EXEC tempdb..sp_columns ‘#Temp’)
Partial results are shown here

Column_name              Type       Computed  Length  Prec  Scale Nullable
--------------------------------------------------------------------------
partition_id             bigint     no        8       19    0     no
object_id                int        no        4       10    0     no
index_id                 int        no        4       10    0     no
partition_number         int        no        4       10    0     no
hobt_id                  bigint     no        8       19    0     no
rows                     bigint     no        8       19    0     no
filestream_filegroup_id  smallint   no        2       5     0     no
data_compression         tinyint    no        1       3     0     no
data_compression_desc    nvarchar   no        120                 yes

Let us try to UPDATE the aliased table

UPDATE #Temp AS T
SET partition_number = partition_number + 1;

Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'AS'.

Is aliasing (“AS”) the issue? Let us remove the “AS” keyword

UPDATE #Temp T
SET partition_number = partition_number + 1;

Msg 102, Level 15, State 1, Line 1
Incorrect syntax near 'T'.

It is still an issue. Let us remove the alias “T”

UPDATE #Temp
SET partition_number = partition_number + 1;
(100 row(s) affected)

That works and we did not need the alias in the simple case above.
No-aliasing = Too much typing:

Consider this case where an aliased name would be preferable

UPDATE MonthlyProcessing.MonthlyCustomerCreditAnalysisBatch
SET    HasPriorTransaction = 1
WHERE  TransactionTypeCode IN ( 'CHG', 'EVT' )
       AND EXISTS
      (
         SELECT 1
         FROM   dailyprocessing.CreditTransaction ct
         WHERE MonthlyProcessing.MonthlyCustomerCreditAnalysisBatch.TransactionId = ct.TransactionId
      );

Here is the trick:

Notice the aliasing in line 3 and the aliased references elsewhere

UPDATE work
SET    HasPriorTransaction = 1
FROM MonthlyProcessing.MonthlyCustomerCreditAnalysisBatch AS work
WHERE  TransactionTypeCode IN ( 'CHG', 'EVT' )
       AND EXISTS
      (
         SELECT 1
         FROM   dailyprocessing.CreditTransaction ct
         WHERE work.TransactionId = ct.TransactionId
      );

We added a FROM clause with the table aliased – FROM MonthlyProcessing.MonthlyCustomerCreditAnalysisBatch AS work and the rest is obvious.

Variation of the trick WITH CTE:

You could also do this with CTE (fancy name for a WITH clause – Common Table Expression)

WITH work
AS
(
   SELECT HasPriorTransaction
   FROM MonthlyProcessing.MonthlyCustomerCreditAnalysisBatch AS work1
   WHERE  TransactionTypeCode IN ( 'CHG', 'EVT' )
       AND EXISTS (SELECT 1
                   FROM   DailyProcessing.CreditTransaction ct
                   WHERE work.TransactionId = ct.TransactionId)
)
UPDATE work
SET HasPriorTransaction = 1;

UPDATE a multi-table join with aliasing:

Yes, you can update a multi-table join with an UPDATE as long as only one table will be updated. Notice the aliasing on the table being updated

UPDATE work
SET HasPriorTransaction = 1
FROM MonthlyProcessing.MonthlyCustomerCreditAnalysisBatch AS work
  INNER JOIN DailyProcessing.CreditTransaction rt
      ON work.TransactionId = ct.TransactionId
  INNER JOIN Common.LookupCode c1
      ON c1.CodeId = rt.TransactionTypeId
WHERE
   c1.CodeValue IN ('LAPSE','OVERDRAFT','DEATH','EXCHANGE','EXPIRED')
   AND rt.IsReversed = 0

I hope you have got the idea and can alias your updates in SQL Server with these simple tips.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s