Update a table column depending on a column from the foreign key table

I have a problem where I have a table A which has a foreign key to table B, both tables contain a From and a createdAt column, both of type datetime.

I want to update the From column in table A to be either the A.From or B.CreatedAt depending on which one is bigger.

How should I approach this kind of problem? Can this only be done using cursor?

Solution

I want to update the From column in table A to be either the A.From or B.CreatedAt depending on which one is bigger.

There's no need to update the From column in table A if A.From is equal to or larger than the corresponding CreatedAt value in table B.

Assuming your tables look something like:

DECLARE @TableA table
(
    ID integer NOT NULL PRIMARY KEY, 
    [From] datetime NOT NULL,
    CreatedAt datetime NOT NULL
);

DECLARE @TableB table
(
    ID integer NOT NULL PRIMARY KEY,
    A_ID integer NOT NULL, -- foreign key
    [From] datetime NOT NULL,
    CreatedAt datetime NOT NULL
);

The update you need is of the form:

UPDATE TA
SET TA.[From] = TB.CreatedAt
FROM @TableA AS TA
JOIN @TableB AS TB
    ON TB.A_ID = TA.ID
WHERE
    TB.CreatedAt > TA.[From];