Title: with-constrained consecutive updates
Tags: sql;sql-server;tsql;sql-server-2012
Question: Please assume I have built a query in MS Sqlserver, it has the following structure:
WITH issues_a AS
SELECT a_prop
FROM ds_X x
, issues_b AS
SELECT key
, z.is_flagged as is_flagged
, some_prop
FROM ds_Z z
JOIN issues_a i_a
ON z.a_diff = i_a.a_prop
-- {{ run }}
UPDATE samples
SET error =
CASE
WHEN i_b.some_prop IS NULL THEN '#1 ...'
WHEN UPPER(i_b.is_flagged) != 'Y' THEN '#2 ...'
END
FROM samples s
left join issues_b i_b ON s.key = i_b.key;
Now I want enhance the whole thing, updating one more table in a consecutive way by enclosing parts of the query in BEGIN TRANSACTION and COMMIT, but don't get my head around the how of it. Tried enclosing the whole expression with the transaction parenthesis, but that didn't bring me any further.
Are there any other ways to achieve the above task - even without concatenating the consecutive updates in a transactional manner, though better it would be?
For abbreviation the task again: WITH <...&gt;(...), <...&gt;(...) UPDATE <... Using data from latter WITH&gt; UPDATE <... using data from latter WITH&gt;?
Hope you don't mind my poor grammar...
Comment: You can only have one update target. For two targets you must have two different updates, each with its own set of `with`s, even if they are identical.

Comment: This solved it! Thanks!

Comment: Why use a CTE for SELECT a_prop FROM ds_X x?  Just join directly on the table.



{'source': 'StackExchangeDataset', 'subset': 'stackoverflow.com', 'metadata': {'AnswerCount': 0, 'CommentCount': 3, 'ContentLicense': 'CC BY-SA 3.0', 'CreationDate': '2014:00:08/15/14', 'FavoriteCount': None, 'PostTypeId': 1, 'Tags': '<sql><sql-server><tsql><sql-server-2012>', 'Title': 'with-constrained consecutive updates', 'ViewCount': 48, 'id': 25323472, 'user_AboutMe': '', 'user_AccountId': 4025626.0, 'user_DisplayName': 'daniel.kahlenberg', 'user_Reputation': 307.0, 'user_Views': 114.0, 'user_id': 3320256.0}}