CODEWITHSUNDEEP
sqlsql-servercommon-table-expression
bds212
bds212

Reputation: 87

Is it possible to use a subquery more than once?

I was wondering if it was possible to use a subquery in SQL more than once.

For example:

with subQuery as (
select id from someTable1
)
update someTable2
set someValue = 1
where id in (select * from subQuery)

select * from someTable2
where id in (select * from subQuery)

...

As of right now, SQL throws an error on the (select * from subQuery) in the select * someTable2 clause saying "Invalid Object Name subQuery". So is there a way to use subQuery more than once without having to add a table or run the query multiple times changing out the first statement?

Upvotes: 1

Views: 473

Answers (1)

David Browne - Microsoft
David Browne - Microsoft

Reputation: 89419

A CTE is in scope only for a single query, but a query can both UPDATE and OUTPUT data. eg

with subQuery as (
select id from someTable1
)
update someTable2
set someValue = 1
output inserted.*
where id in (select * from subQuery)

Upvotes: 1

Related Questions