Update MySQL column based on another column's data

Question

I have a table named TICKET. Among the columns in ticket are two date fields:

TICKET
----------------------------------------
TICKDATE             | ELSDATE     | ...
---------------------+-------------+----
yyyy-mm-dd hh:mm:ss    yyyy-mm-dd

These two dates should always be the same.

I have a range of dates (from 7/1 to current) where ELSDATE is null. I'm just starting out in sql scripting, and I don't know how to update one column to equal the other. The current script I'm running to update one day at a time is:

update TICKET ELSDATE="yyyy-mm-dd" where date(TICKDATE)="yyyy-mm-dd"

I assume I'd be able to do something like:

update TICKET where ELSDATE="???" date(TICKDATE)<date("2015-09-29")

If they should always be the same, why don't you just remove that column? It is taking up space, without giving you any benefit. — Sumurai8
– Sumurai8, Commented Sep 29, 2015 at 15:58
@Sumurai8 TICKDATE and ELSDATE are written to from two applications at remote sites. TICKDATE is read by a monitoring application both on-site and remotely. ELSDATE is polled by our business intelligence infrastructure. It's very stupid, but it's well-established stupid which is difficult to change. — Adam Smith
– Adam Smith, Commented Sep 29, 2015 at 16:07

woz · Accepted Answer · 2015-09-29 16:41:57Z

6

If you want to set every value of ELSDATE to the value of TICKDATE in the same row where TICKDATE is before September 29, 2015, use this:

UPDATE TICKET SET ELSDATE = TICKDATE where TICKDATE < "2015-09-29"

edited Sep 29, 2015 at 16:41

answered Sep 29, 2015 at 16:06

woz

11.1k3 gold badges37 silver badges65 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Arth Over a year ago

This cannot use any index on TICKDATE.. I'd suggest TICKDATE < "2015-09-29"

Adam Smith Over a year ago

@Arth Can you explain? I'm not sure what that means.

Arth Over a year ago

If the column TICKDATE has an index, a query that directly compares it can use the index to find the rows that match the comparison. This is preferable in performance terms to accessing every row to find the rows that match.

crafter · Accepted Answer · 2015-09-29 16:36:38Z

3

You can do all updates in a single query so you don't have to do them one day at a time. The query will change each row ELSDATE to equal TICKDATE

UPDATE TICKET SET ELSDATE = TICKDATE;

To only update rows where ELSEDATE is null, you can use.

UPDATE TICKET SET ELSDATE = TICKDATE WHERE ELSDATE IS NULL;

To change rows between 2 dates, use the BETWEEN clause.

UPDATE TICKET SET ELSDATE = TICKDATE WHERE TICKDATE BETWEEN '2015-07-01' AND CURDATE();

edited Sep 29, 2015 at 16:36

answered Sep 29, 2015 at 16:13

crafter

6,2971 gold badge39 silver badges47 bronze badges

6 Comments

Adam Smith Over a year ago

Will the last statement include CURDATE()? It's important that ELSDATE is still null on data from today.

C4d Over a year ago

@AdamSmith It will. If you dont want to, use this Where-Clause: ...WHERE TICKDATE > '2015-07-01' AND TICKDATE < CURDATE();. This will exclude CURDATE() from the query.

Adam Smith Over a year ago

@C4ud3x Thanks for the clarification! In this instance, I can update all the rows older than today so I don't have to worry about that at all. That said, it's great to know what options I have!

Arth Over a year ago

Your final update is wrong, the BETWEEN will convert the CURDATE() into a TIMESTAMP/DATETIME for comparison with TICKDATE (effectively appending 00:00:00). You are better of using two conditions with >= and <. I personally never use BETWEEN for date/time comparisons for this reason.

crafter Over a year ago

@Arth, I was mistaken. Thanks for pointing out. Editing my post.

|

Arth · Accepted Answer · 2015-09-29 16:21:32Z

2

Yep:

UPDATE TICKET 
   SET ELSDATE = TICKDATE 
 WHERE TICKDATE < CURDATE() /* AND ELSDATE IS NULL */

answered Sep 29, 2015 at 16:21

Arth

13.1k5 gold badges43 silver badges72 bronze badges

Collectives™ on Stack Overflow

Update MySQL column based on another column's data

3 Answers 3

3 Comments

6 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

6 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related