At Percona’s web store you can order your MySQL database support needs in minutes. No hassles, no fuss, just pick and click. Pay online with a credit card.
COURSE of the MONTH
10 days, 6 hours left to enrollBecome a Premium Member and unlock a new, free course in leading technologies each month.
Solved
What is wrong with this MySQL statement?
Posted on 2011-09-17
The statement below does not give an error code, but it does not compute the TIMEDIFF:
"UPDATE Stop_times, Start_times
SET Stop_times.Project_Charge_
WHERE Start_times.PROJECTid = Stop_times.PROJECTid
AND
Start_times.EMPLOYid=Stop_
but if you delete the update and modify it to a select statement like '/**UPDATE Stop_times, Start_times
SET Stop_times.Project_Charge_
FROM Stop_times, Start_times
WHERE Start_times.PROJECTid = Stop_times.PROJECTid
AND
Start_times.EMPLOYid=Stop_
it computes the time difference correctly
"UPDATE Stop_times, Start_times
SET Stop_times.Project_Charge_
WHERE Start_times.PROJECTid = Stop_times.PROJECTid
AND
Start_times.EMPLOYid=Stop_
but if you delete the update and modify it to a select statement like '/**UPDATE Stop_times, Start_times
SET Stop_times.Project_Charge_
FROM Stop_times, Start_times
WHERE Start_times.PROJECTid = Stop_times.PROJECTid
AND
Start_times.EMPLOYid=Stop_
it computes the time difference correctly
[X]
Welcome to Experts Exchange
Add your voice to the tech community where 5M+ people just like you are talking about what matters.
- Help others & share knowledge
- Earn cash & points
- Learn & ask questions
4-
3
7 Comments
Active 5 days ago
Hi. You said it does not throw an error; therefore, how are you judging that it did not compute?
Active 5 days ago
The syntax you have looks fine, so suspect something else. For example, by naming convention, I would not expect the actual time values to be in a column STARTid or STOPid. When I see that I think AUTO_INCREMENT or INT fields that are keys to a table. Try the below syntax ensuring that the columns in TIMEDIFF are indeed DATETIME columns. Since you said this works in SELECT, that may be the STARTid and STOPid, but may be worth double checking.
UPDATE Stop_times stp
JOIN Start_times strt
ON strt.PROJECTid = stp.PROJECTid
AND strt.EMPLOYid = stp.EMPLOYid
SET stp.Project_Charge_Time = TIMEDIFF(stp.STOPdatetime, strt.STARTdatetime)
;
I can see that it does not work because stp.Project_Charge_Time is 0000-00-00 00:00:00 also after running the query. Your alternative code gives the same result. (When I modified stp.STOPdatetime to STOPid and the same for STARTid)
Active 5 days ago
Wait. Project_Charge_Time is a datetime?
Remember TIMEDIFF is given you a difference in time values. It is not resulting in a datetime value. Please show the table structure(s) and some sample data with what you expect the final results to be.
Remember TIMEDIFF is given you a difference in time values. It is not resulting in a datetime value. Please show the table structure(s) and some sample data with what you expect the final results to be.
Featured Post
Choosing the right backup solution for your organization can be a daunting task. To make the selection process easier, ask solution providers these 10 key questions.
Question has a verified solution.
If you are experiencing a similar issue, please ask a related question
Containers like Docker and Rocket are getting more popular every day. In my conversations with customers, they consistently ask what containers are and how they can use them in their environment. If you’re as curious as most people, read on. . .
Backups and Disaster RecoveryIn this post, we’ll look at strategies for backups and disaster recovery.
- Server Hardware
- IT Administration
- Disaster Recovery
- Storage Software
- Databases
- Storage Hardware, MySQL Server, Hardware, Percona, *Backups
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster.
To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment.
To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
Suggested Courses
Course of the Month10 days, 6 hours left to enroll
762 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.