Select and update in same stored procedure and using result of select to update some value

Question

Is there anything like

select Column_n from table1;

update table2 set column_m where column_a=Column_n ;

Which can be written in "same stored" procedure

Answer 1

You can do this

Declare @Var = varchar(50)

select @Var = Column_n from table1;

update table2 set column_m = newValue where column_a= @Var;

Answer 2

SQL Server has a lot of options for updating one table using data from other tables. Bellow you could find some of these solutions:

CREATE TABLE TableX
(
    ID INT NOT NULL,
    Col2 VARCHAR(10)
);
INSERT TableX (ID)
SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3;

CREATE TABLE TableY
(
    ID INT NOT NULL,
    Col2 VARCHAR(10)
);
INSERT TableY (ID,Col2)
SELECT 1,'A' UNION ALL SELECT 2,'B' UNION ALL SELECT 4,'D';

Solutions:

-- Solution  #1
UPDATE TableX SET Col2 = NULL;
UPDATE  x
SET     Col2 = y.Col2
FROM    TableX x INNER JOIN TableY y ON x.ID = y.ID;
SELECT  * FROM TableX;
/*
ID          Col2
----------- ----
1           A
2           B
3           NULL
*/

-- Solution  #2
UPDATE TableX SET Col2 = NULL;
UPDATE  TableX 
SET     Col2 = 
        (
            SELECT  y.Col2
            FROM    TableY y 
            WHERE   TableX.ID = y.ID
        )
SELECT  * FROM TableX;
/*
ID          Col2
----------- ----
1           A
2           B
3           NULL
*/

-- Solution  #3
UPDATE TableX SET Col2 = NULL;
WITH UpdateTableX
AS
(
    SELECT  x.Col2 AS TargetCol2,
            y.Col2 AS SourceCol2
    FROM    TableX x INNER JOIN TableY y ON x.ID = y.ID
)
UPDATE  UpdateTableX
SET     TargetCol2 = SourceCol2
SELECT  * FROM TableX;
/*
ID          Col2
----------- ----
1           A
2           B
3           NULL
*/

-- Solution  #4 (SQL2008+)
UPDATE TableX SET Col2 = NULL;
MERGE INTO TableX x
USING TableY y ON x.ID = y.ID
WHEN MATCHED THEN
    UPDATE SET Col2 = y.Col2;
SELECT  * FROM TableX;  
/*
ID          Col2
----------- ----
1           A
2           B
3           NULL
*/

Now, if for target table one row can match many rows from source table then you could have problems because some solutions can become UNSAFE (solutions #1 and #3)

Example: I insert another row in source table (TableY)

INSERT TableY (ID,Col2)
SELECT 1,'AA'

Now, for one row with ID=1 from source table there are 2 rows with ID=1 ({1,'A'} and {1,'AA'}). In this case, the solutions #1 or #3 will be executed successfully even these UPDATEs are unsafe:

UPDATE TableX SET Col2 = NULL;
UPDATE  x
SET     Col2 = y.Col2
FROM    TableX x INNER JOIN TableY y ON x.ID = y.ID;
SELECT  * FROM TableX;
/*
ID          Col2
----------- ----------
1           A <-- In my test, SQL Server selected "first" row with ID=1 from source table (TableY) which has 2 rows with ID=1 ({1,'A'} and {1,'AA'})
2           B
3           NULL
*/

But if I change the order of rows in the source table TableY the results of he same UPDATE statements will be different:

TRUNCATE TABLE TableY;
INSERT TableY (ID,Col2)
SELECT 1,'AA' UNION ALL SELECT 2,'B' UNION ALL SELECT 4,'D';
INSERT TableY (ID,Col2)
SELECT 1,'A';

UPDATE TableX SET Col2 = NULL;
UPDATE  x
SET     Col2 = y.Col2
FROM    TableX x INNER JOIN TableY y ON x.ID = y.ID;
SELECT  * FROM TableX;
/*
ID          Col2
----------- ----------
1           AA <-- The same UPDATE gives different results: 'AA' instead of 'A'
2           B
3           NULL
*/

If I execute solutions #2 and #4 then I will receive an exception (which is good)

Solution #2:
Msg 512, Level 16, State 1, Line 17
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
The statement has been terminated.

Solution #4:
Msg 8672, Level 16, State 1, Line 55
The MERGE statement attempted to UPDATE or DELETE the same row more than once. This happens when a target row matches more than one source row. A MERGE statement cannot UPDATE/DELETE the same row of the target table multiple times. Refine the ON clause to ensure a target row matches at most one source row, or use the GROUP BY clause to group the source rows.

because I know that I have to rewrite those statements

-- Solution  #2
UPDATE TableX SET Col2 = NULL;
UPDATE  TableX 
SET     Col2 = 
        (
            SELECT  MAX(y.Col2) -- or MIN or something else
            FROM    TableY y 
            WHERE   TableX.ID = y.ID
        )

-- Solution  #4 (SQL2008+)
UPDATE TableX SET Col2 = NULL;
MERGE INTO TableX x
USING 
(
    SELECT  a.ID, MAX(a.Col2) AS Col2 -- or MIN or something else
    FROM    TableY a
    GROUP BY a.ID
) y ON x.ID = y.ID
WHEN MATCHED THEN
    UPDATE SET Col2 = y.Col2;

Answer 3

You can UPDATE with JOIN directly like this:

UPDATE t2
SET t2.column_m = ...
FROM table2 AS t2
INNER JOIN table1 AS t1 ON t1.column_n = t2.column_a;

You can put it inside a stored procedure.