i need help on update

[akuvidz]

I need help on my update statement,

the condition is , i have to update the status .

the table name is leave_request , it contain leave_req_id, date_applied,emp_id,leave_type_id,leave_period_id,leave_status

and the default for leave_status is 'Pending'

the problem is, choose approve, and on the database should change the leave_status into 'Approve' ,

It change, but it change the whole row, how can i write the update statement so it can only affect 1 row?

[BitBlt]

Your request is a little...confusing. And, you didn't post the statement you are actually using. It's kind of hard to help when you don't include it.

However (and I'm going out on a limb here) it sounds like your update statement isn't using a "where" clause to isolate the one row you're interested in updating. Here's a snippet to hopefully point you in the right direction:

UPDATE myTable
set myCol1 = 'value1',
myCol2 = 'value2'
where myTable.KeyCol = 12345

If I'm incorrect in my assumptions, please post the SQL statement you're using, and we'll help if we can.

Good luck!

[lps]

Based on my understanding, do you mean something like the whole column of 'leave_status' is changed for every row? If so, I think the main problem is the 'WHERE' condition of your query. You should set a 'WHERE' with a clear condition (better using primary key of the table) to identify the correct row to update. In your case, I not quite understand on your data structure but I assumed that 'leave_req_id' is unique, so basically you have to update the 'leave_status' based on that.

[xjshiya]

Use the 'WHERE' clause.
UPDATE leave_request
SET leave_status = 'Approve'
WHERE leave_req_id = '00001' // you can replace the 'leave_req_id = '00001' with whatever condition you wish, to change a specific row you want.

[akuvidz]

i have another question to ask, its same with update, but with different table..

i want to update no_of_days_alloted from the table employee_leave_quota..

and i take the list of employee from employee table .

i put emp_id as foreign key in employee_leave_quota..

here is the interface what will it look like ...

EMP ID EMP NAME EMP No of days alloted ACTION
1 HARUYA <this is textbox to input number> <this is the link to update>

so everytime user input number on the textbox, and click the link, it will update the no of days alloted for the EMP id number 1..

but it seems like my update statement is wrong,

here is my update statement..

UPDATE employee_leave_quota 
LEFT JOIN employee
ON employee.emp_id = employee_leave_quota.emp_id
SET employee_leave_quota.no_of_days_alloted = '$add'
WHERE employee.emp_id = '$empid'

I got this from other web, but it doesnt work out.

can anyone help me?

[BitBlt]

Did your original question get solved? If so, let us know. You should start another thread if you have a new question.

So...

Without knowing more about your table structure, it's difficult to know why your update statement isn't working. Not only that, but you give no information about what error your new query is throwing. More detail is needed for anyone to help you.

However, I can say that trying to update based on a left join is going to be "iffy" becaues any left join leaves the possibility of a NULL result. You may want to rethink your scenario before using that construct.