Jump to content

Need Help


jana

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?????????
Link to comment
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
Link to comment
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]
Link to comment
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
Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

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.