Jump to content


Photo

Need Help


  • Please log in to reply
5 replies to this topic

#1 jana

jana
  • Members
  • PipPipPip
  • Advanced Member
  • 35 posts
  • LocationIndia

Posted 24 October 2006 - 10:32 AM

i am having two tables ltime and rtime,

ltime
(emp_id    ttime)
as001      00:00:00
as002      00:00:00
as003      00:00:00


rtime
(emp_id    ttime)
as001      09:10:00
as002      09:05:00
as003      09:16:00

and i want to update the ltime table

and i used subqery as

update table ltime set ttime=(select addtime(ltime.ttime, timediff(rtime.ttime, '09:10')) from ltime, rtime where ltime.emp_id=rtime.emp_id and rtime.ttime>'09:10');

and i am getting error in this.

can anyone help me to solve this?????????
Jana

#2 jana

jana
  • Members
  • PipPipPip
  • Advanced Member
  • 35 posts
  • LocationIndia

Posted 24 October 2006 - 11:33 AM

i just made one change and it's working now but not as exactly i wanted.

and the change is

update ltime set ltime.ttime=(select addtime(ltime.ttime, timediff(rtime.ttime, '09:10')) from rtime where ltime.emp_id=rtime.emp_id and rtime.ttime>'09:10');

but when i changed the rtime.ttime values, the previous values of ltime.ttime are not added to current value.

ex:

rtime
(emp_id          ttime)
as001            09:15:00
as002            09:06:00
as003 09:11:00

ltime
(emp_id          ttime)
as001            00:06:00
as002            00:02:00
as003            00:00:00

here i want to add the ltime.ttime with timediff(rtime.ttime, '09:10'), but i got the result as

ltime
emp_id          ttime
as001            00:05:00
as002            00:00:00
as003            00:01:00
Jana

#3 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 24 October 2006 - 03:41 PM

Run the subquery alone, and see if you're calculating the new times correctly first.
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.

#4 jana

jana
  • Members
  • PipPipPip
  • Advanced Member
  • 35 posts
  • LocationIndia

Posted 25 October 2006 - 11:58 AM

The subquery is running fine but, when i include it with the update query it returns a warning message. i viewed the warning message by show warnings. it says that column set to default value, Null supplied to not null column ttime at row 2.
Jana

#5 shoz

shoz
  • Staff Alumni
  • Advanced Member
  • 600 posts

Posted 25 October 2006 - 12:58 PM

The subquery is running fine but, when i include it with the update query it returns a warning message. i viewed the warning message by show warnings. it says that column set to default value, Null supplied to not null column ttime at row 2.

update ltime set ltime.ttime=(select addtime(ltime.ttime, timediff(rtime.ttime, '09:10')) from rtime where ltime.emp_id=rtime.emp_id and rtime.ttime>'09:10');


Although I can't currently find a reference in the manual to support this, the subquery above would (I believe) be run for each entry in the ltime table. When the subquery returns no results for an ltime entry because the WHERE clause can't be satisfied, the "Warning" that you quoted occurs and a possibly undesirable value set.

You should be able to use the following
UPDATE
ltime
INNER JOIN 
rtime 
ON
ltime.emp_id=rtime.emp_id
SET
ltime.ttime= ADDTIME(ltime.ttime, TIMEDIFF(rtime.ttime, '09:10'))
WHERE
rtime.ttime > '09:10'

http://dev.mysql.com.../en/update.html

#6 jana

jana
  • Members
  • PipPipPip
  • Advanced Member
  • 35 posts
  • LocationIndia

Posted 26 October 2006 - 03:34 AM

it's working fine, thanx alot.
Jana




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users