Conditionally update column from another column in same table-CodePudding

I've looked through SO, and there are similar questions, but I can't seem to figure out how to do what I need.

For the purposes of this question, my table has 3 columns: reconciled (tinyint), datereconciled (timestamp, CAN BE NULL), and dateadded (timestamp).

For my code logic, if reconciled==1, there should be a timestamp in datereconciled, but I recently noticed that wasn't always happening. Fixed the code, but now have a lot of NULL values in datereconciled where there should be a timestamp. So, for all rows where reconciled==1 AND datereconciled==NULL, I would like to "update" the value FROM dateadded INTO datereconciled. If there is already a timestamp in datereconciled, leave it alone. And leave it alone if reconciled==0.

CodePudding user response：

You should be able to use a simple update:

UPDATE YourTable 
SET DateReconciled = DateAdded 
WHERE DateReconciled IS NULL 
AND reconciled = 1;

CodePudding user response：

You basically wrote the query already

UPDATE table SET datereconciled = dateadded 
WHERE reconciled = 1
AND datereconciled IS NULL

CodePudding user response：

I figured I'd have to use a select in my update query, so I'm a victim of over-complicating things! However, here is my overly complicated self-discovered answer prior to the answers provided:

UPDATE
    `transactions` AS `dest`,
    (
        SELECT
            *
        FROM
            `transactions`
        WHERE
            `reconciled` = 1 AND `datereconciled` IS NULL
    ) AS `src`
SET
    `dest`.`datereconciled` = `src`.`dateadded`
;