SQL - Update statement in procedure - dependent filed gets filled only on second run of the procedur-CodePudding

Here is my update procedure:

UPDATE tbl1
SET
    -- other columns
    tbl1.field_1 = CONVERT(datetime, CONVERT(varchar(10), carsAll.DateOfBirth, 104)),
    tbl1.field_2 = DATEPART(YEAR, GETDATE()) - DATEPART(YEAR, tbl1.field_1),
    -- other columns
FROM 
    [db1].[cars].[table_1] AS tbl1
INNER JOIN 
    [db2].[cars].[carsAll_test] AS carsAll ON tbl1.[TP_PK]=carsAll.[TP_PK]

First time I run procedure all columns gets filled with data except tbl1.field_2.

Second time I run it tbl1.field_2 gets filled as well.

How do I update all columns in one run and why is this happening when tbl1.field_1 is set before procedure starts with tbl1.field_2 ?

I'm using SQL Server 2017 (v14).

CodePudding user response：

why is this happening when tbl1.field_1 is set before procedure starts with tbl1.field_2

The field on the right side of your query (tbl1.field_1) will refer to its original value(before update), regardless of the order they are in.

In simple words, the updated value for tbl1.field_1 will be available only after the whole update statement in completed. So, you can't use this line in this same update statement tbl1.field_2 = DATEPART(YEAR, GETDATE()) - DATEPART(YEAR, tbl1.field_1),

How do I update all columns in one run

Instead of referencing tbl1.field_1, you can directly reference carsAll.DateOfBirth

UPDATE tbl1
SET
    -- other columns
    tbl1.field_1 = CONVERT(datetime, CONVERT(varchar(10), carsAll.DateOfBirth, 104)),
    tbl1.field_2 = DATEPART(YEAR, GETDATE()) - DATEPART(YEAR, carsAll.DateOfBirth),
    -- other columns
FROM 
    [db1].[cars].[table_1] AS tbl1
INNER JOIN 
    [db2].[cars].[carsAll_test] AS carsAll ON tbl1.[TP_PK]=carsAll.[TP_PK]