0

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]"
2

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.

0

Use local Temp Table in your procedure:

SELECT id INTO #X
FROM ABC WHERE MyCriteria = 'XYZ';

SELECT * FROM ABC 
WHERE Cancelled = 'Yes' and 
    id in (SELECT id FROM #X);

UPDATE ABC SET MyColumn = 'Updated'
WHERE Cancelled = 'Yes' and 
    id in (SELECT id FROM #X);

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.