skip to Main Content

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.

3

Answers


  1. Chosen as BEST ANSWER

    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`
    ;
    

  2. You basically wrote the query already

    UPDATE table SET datereconciled = dateadded 
    WHERE reconciled = 1
    AND datereconciled IS NULL
    
    Login or Signup to reply.
  3. You should be able to use a simple update:

    UPDATE YourTable 
    SET DateReconciled = DateAdded 
    WHERE DateReconciled IS NULL 
    AND reconciled = 1;
    
    Login or Signup to reply.
Please signup or login to give your own answer.
Back To Top
Search