SQL - Use a Reference of a Cte to Another Cte

SQL - Use a reference of a CTE to another CTE

Here are three important properties of CTEs:

• You can refer to a CTE in subsequent CTEs or in the main body of the query.

• You can refer to any given CTE multiple times.

• The CTE can be used in a from clause at any level of nesting within other subqueries.

The CTEs -- as with everything in SQL -- need to be defined before they are used. So, you cannot define them in random order.

This is the standard definition of CTEs and does a good job of explaining how they are used across databases. Those three properties are key ways that they differ from subqueries.

How to create CTE which uses another CTE as the data to further limit?

You can chain 2 (or more) CTE's together.

For example

with ObjectsWithA as
(
  select * from sys.objects
  where name like '%A%'
),
ObjectsWithALessThan100 as
(
  select * from ObjectsWithA
  where object_id < 100
)
select * from ObjectsWithALessThan100;

Or the same example, with more "spelled out" names/aliases:

with ObjectsWithA (MyObjectId , MyObjectName) as
(
  select object_id as MyObjIdAlias , name as MyNameAlias 
  from sys.objects
  where name like '%A%'
),
ObjectsWithALessThan100 as
(
  select * from ObjectsWithA theOtherCte
  where theOtherCte.MyObjectId < 100
)
select lessThan100Alias.MyObjectId , lessThan100Alias.MyObjectName 
from ObjectsWithALessThan100 lessThan100Alias 
order by lessThan100Alias.MyObjectName;

Call one CTE in another CTE

You have to join both like with a normal table:

WITH cte1 
     AS (SELECT city.* 
         FROM   city 
         WHERE  ( city.cityname COLLATE sql_latin1_general_cp1_ci_ai ) LIKE 
                'são paulo'), 
     cte2 
     AS (SELECT imovel.imovel_id 
         FROM   imovel 
                INNER JOIN cte1 
                        ON imovel.city_id = cte1.city_id 
         WHERE  imovel.number = 311 
                AND imovel.zipcode = '30280490' 
                AND imovel.complement = '' 
                AND imovel.street = 'Do furquim') 
SELECT * FROM   cte2 

Note that i have appended SELECT * FROM cte2 since CTE's cannot "stand" alone.

How can I add the outputs of one CTE to another CTE?

Have you overcomplicated your code? Why do you traverse the hierarchy at all? Surely if you intend to delete "a" when "b" reports to "a" and "c" reports to "b", you don't intend to do anything to "c". You only need to change those reporting directly to "a" ("b" in this case). If so, you don't need CTEs to traverse the hierarchy. You have also learned bad habits and have chosen strange naming standards.

Just check for the existence of @empId1 (a generic name that does not provide any clues about how it is used - see what I mean by naming standards?) as a supervisor first and "move" (not "add") those rows to the other user parameter @empId2. In short:

update t_employee set Super_ssn = @empId2
where Super_ssn = @empId1;

delete t_employee where Ssn = @empId1;

That is all the code you need at a very basic level. Add whatever error handling you wish, perhaps some sanity checking of the parameter values, and perhaps check for existence before the update. Use of two part names (schema.table) is a best practice to develop.

How to use the results of a CTE query inside another query

Do you just mean this:

;with test_cte(id,name) as
(
  select id,name from dbo.test
)
delete test_cte where name='john';

Do you want to delete rows and show the rows you deleted?

;with test_cte(id,name) as
(
  select id,name from dbo.test
)
delete test_cte 
output deleted.id, deleted.name
where name='john';

• Example db<>fiddle

To take your explicit example:

delete from test
where id in (
    with test_cte (id,name) as (
        select id, name
        from test
    )
    select id
    from test_cte
    where name = 'john'
)

You're getting a syntax error because, well, there's an error in your syntax. CTE must be defined up front, not in any random or arbitrary point in your query.

;with test_cte (id,name) as (
    select id, name
    from test
)
delete from test
where id in (
    select id
    from test_cte
    where name = 'john'
)

But this still seems awfully over-complicated compared to the simpler examples I've shown.

CTE within a CTE

You can't nest CTEs like that in SQL Server but you can use multiple CTEs the following way:

;with test as 
(
    select 
        SRnum, 
        gamenumber, 
        StartOfDistribution, 
        ApplicationNumber   
    from #main
    where startofdistribution = '2011-06-14 00:00:00.000'
    and SRnum = '313'
    --order by SRnum, gamenumber, StartOfDistribution, ApplicationNumber
),
 outertest as
 (
    select
        ApplicationNumber
        ,count(*) as RetailerAppearance
    from test
    group by ApplicationNumber
    having count(*) = 4
) 
select count(*) 
from outertest

---

Related Topics