Sql-server – Using results from one query as the parameters for another

sql serversubquery

Is there a way to have a SQL query automatically update its' parameters based on the results of a previous query?

Here's the scenario I'm trying to achieve:

A report should automatically be run daily that runs the following queries (in order):

  1. SELECT id of rows that match a certain criteria (we'll call this result set "X")
  2. SELECT certain fields from rows that are titled "Cancelled-[X]"
  3. UPDATE rows from [X] with information from "Cancelled-[X]"

Best Answer

Consider using Common Table Expressions (CTEs).

I don't have your table structures nor your queries, but what I would envisage is something like this:

WITH cte1 AS
(
  Perform your 1.SELECT id of rows that match a certain criteria (we'll call this result set "X")`
),
cte2 AS
(
  Perform you 2.SELECT certain fields from rows that are titled "Cancelled-[X]"
)
UPDATE rows from [X] with information from "Cancelled-[X]";

There are some restrictions on CTEs. You can't perform UPDATEs from them for example. I'm not really a MS SQL Server person, but here are a couple of references which should help (1, 2).

For an example of them in action, see my post here. It's a PostgreSQL solution, but the MS SQL Server syntax is virtually identical.

Related Question