Question 113120 in Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – Conditionally update column from another column in same table

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.

Tags:

3

Answers


  1. Chosen as BEST ANSWER
    0

    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. 0

    You basically wrote the query already

    UPDATE table SET datereconciled = dateadded 
WHERE reconciled = 1
AND datereconciled IS NULL
    Login or Signup to reply.
  3. 0

    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