How to use Common Table Expressions In SQL Server

Common Table Expressions offer the same functionality as a view,But you can select,delete,update tables using CTE and you do not have to store definition metadata 

CREATE TABLE #TABLECTE(NAME VARCHAR(20),AGE INT); --Temp Table Creation

INSERT INTO #TABLECTE VALUES('RAFEL',20);

INSERT INTO #TABLECTE VALUES('JAMES',18);

INSERT INTO #TABLECTE VALUES('JOHN',20);

INSERT INTO #TABLECTE VALUES('SYAM',21);

INSERT INTO #TABLECTE VALUES('JACK','');

WITH CTE_Sample (EMP_ID,EMP_NAME,AGE)

AS

(

      SELECT ROW_NUMBER() OVER(ORDER BY NAME), NAME,AGE from #TABLECTE

     

)

--SELECT *  FROM CTE_Sample  --- TO SELECT CTE WITH ROWNUMBER

--WHERE AGE=20

 --UPDATE CTE_Sample  ----  UPDATE #TABLECTE USING  CTE CTE

 --SET AGE=40

 --WHERE EMP_NAME='JACK'

 --DELETE  FROM CTE_Sample --- DELETE #TABLECTE USING CTE

 --WHERE AGE=21 

 SELECT * FROM #TABLECTE ---- SELECT AFTER TABLE OPERATION

DROP TABLE #TABLECTE