Need Help

[jana]

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?????????

[jana]

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

[fenway]

Run the subquery alone, and see if you're calculating the new times correctly first.

[jana]

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]

[shoz]

[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

[jana]

it's working fine, thanx alot.