CODEWITHSUNDEEP
sqlsql-servercommon-table-expression
LOL
LOL

Reputation: 111

SQL Server CTE to update a column

I have two tables , table1 and table2 with the following structure

Table1:

ID    Location     Date
----------------------------    
 1      abc        2014-6-3
 2      xyz        2013-6-5

Table2:

ID Location    Date
----------------------    
1    abc       NULL
2    xyz       NULL
3    hgf       2012-9-8

I need to write a CTE to update the Table2.Date column with the values from table1.Date column based on the condition where table1.location = table2.location

Can someone please help me with it as I am very new to the concept of CTE

Upvotes: 0

Views: 4085

Answers (3)

satheesh
satheesh

Reputation: 71

Problem Analysis : The Update using Common Table Expression is possible only when the updated record affects one base base and this is not possible when the update statement affects more than one base table. In the Above query, Update action is possible by using CTE.

Solutions:

With Data(t2Date,t2Location,t1Date,t1Location)
as
  (
   Select t2.Date,t2.Location,t1.Date,t1.Location 
   from Table_2 t2 
   Join Table_1 t1 ON t1.Location = t2.Location
  )

Update Data
SET t2Date = t1Date where t1Location = t2Location

The output will be

enter image description here

Alternate Solution : we can also provide solution by a normal SQL Query without using CTE.

Update Table_2 t2
SET 
t2.Date = (select t1.Date From Table_1 t1 Where t1.Location = 
t2.Location)

Upvotes: 0

HotblackDesiato
HotblackDesiato

Reputation: 348

You can give an alias to the new value in the cte and then update the value using that alias:

WITH cte AS
(
    SELECT
     t2.Date
    ,t1.Date AS NewDate
    FROM #Table1 t1
    JOIN #Table2 t2
    ON t1.Location=t2.Location
)
UPDATE cte
SET Date=NewDate;

Upvotes: 1

ngrashia
ngrashia

Reputation: 9904

You can do it even without CTE as below:.

UPDATE TABLE2 T2
SET 
T2.DATE = (SELECT T1.DATE FROM TABLE1 T1 WHERE T1.LOCATION = T2.LOCATION)
WHERE T2.DATE IS NOT NULL

Upvotes: 0

Related Questions