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.