jana Posted October 24, 2006 Share Posted October 24, 2006 i am having two tables ltime and rtime, ltime(emp_id ttime)as001 00:00:00as002 00:00:00as003 00:00:00rtime(emp_id ttime)as001 09:10:00as002 09:05:00as003 09:16:00and 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????????? Quote Link to comment https://forums.phpfreaks.com/topic/24913-need-help/ Share on other sites More sharing options...
jana Posted October 24, 2006 Author Share Posted October 24, 2006 i just made one change and it's working now but not as exactly i wanted.and the change isupdate 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:00as002 09:06:00as003 09:11:00ltime(emp_id ttime)as001 00:06:00as002 00:02:00as003 00:00:00here i want to add the ltime.ttime with timediff(rtime.ttime, '09:10'), but i got the result as ltimeemp_id ttimeas001 00:05:00as002 00:00:00as003 00:01:00 Quote Link to comment https://forums.phpfreaks.com/topic/24913-need-help/#findComment-113581 Share on other sites More sharing options...
fenway Posted October 24, 2006 Share Posted October 24, 2006 Run the subquery alone, and see if you're calculating the new times correctly first. Quote Link to comment https://forums.phpfreaks.com/topic/24913-need-help/#findComment-113682 Share on other sites More sharing options...
jana Posted October 25, 2006 Author Share Posted October 25, 2006 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 Link to comment https://forums.phpfreaks.com/topic/24913-need-help/#findComment-114146 Share on other sites More sharing options...
shoz Posted October 25, 2006 Share Posted October 25, 2006 [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]UPDATEltimeINNER JOIN rtime ONltime.emp_id=rtime.emp_idSETltime.ttime= ADDTIME(ltime.ttime, TIMEDIFF(rtime.ttime, '09:10'))WHERErtime.ttime > '09:10'[/code]http://dev.mysql.com/doc/refman/4.1/en/update.html Quote Link to comment https://forums.phpfreaks.com/topic/24913-need-help/#findComment-114174 Share on other sites More sharing options...
jana Posted October 26, 2006 Author Share Posted October 26, 2006 it's working fine, thanx alot. Quote Link to comment https://forums.phpfreaks.com/topic/24913-need-help/#findComment-114574 Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.