Jump to content

Archived

This topic is now archived and is closed to further replies.

jana

Need Help

Recommended Posts

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

[color=red]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');[/color]

and i am getting error in this.

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

Share this post


Link to post
Share on other sites
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

Share this post


Link to post
Share on other sites
Run the subquery alone, and see if you're calculating the new times correctly first.

Share this post


Link to post
Share on other sites
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 [color=red]column set to default value, Null supplied to not null column ttime at row 2.[/color]

Share this post


Link to post
Share on other sites
[quote author=jana link=topic=112512.msg457325#msg457325 date=1161777491]
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 [color=red]column set to default value, Null supplied to not null column ttime at row 2.[/color]
[/quote]
[quote author=jana link=topic=112512.msg456742#msg456742 date=1161689605]
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');
[/quote]

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
[code]
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'
[/code]

http://dev.mysql.com/doc/refman/4.1/en/update.html

Share this post


Link to post
Share on other sites

×

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.