Field ID is INT autoincrement as usual for ID field, field value is common INT. I need to insert new record to it. The inserted value should be equal to the highest value +1. I tried to do something like this:
INSERT INTO testtab SET value=(select max(value) from testtab)+1
Mysql threw this error:
You can't specify target table for update in FROM clause
I searched internet and found that I cannot use the same table in a subqery of insert/delete/update query. So my question is: Is here some workaround for this? I know I could use two queries - one to find the maximum value and second to insert the value+1 into DB, but I would like to do it in a single query.
Wow, it works. But I don’t understand it. It is also some sort of subqeery, isn’t it? What does it mean “(value)”? Please, explain it to me or give me some links, I would like to understand it
hi
i hvae seen the threads you sent. iam very happy to get the half answer for the posted thread.
But i need with multiple columns inserting…
i have written the query as like this,
i created one more column in the table mentioned above,
Query:
insert into testtab(value,name) values(select max(value)+1 from testtab,‘sunil’)
please let me know exact query syntax. Its very urgent in my application.
Preventing Duplicate Record Insertion on Page Refresh
i have insert query and form in one page
After clicking submit by the user, one record is inserting…, again if he refreshes the page one more record is inserting.
iam not having any unique colums in the table
hi
i had one more problem,
i have insert query is like this
mysql_query(“INSERT INTO pre_admission_form (adm_no,pre_adm_number, f_name, l_name) SELECT MAX(adm_no)+1,‘Pre’||MAX(adm_no)+1,'”.$_POST[‘f_name’].“', '”.$_POST[‘l_name’].“’ FROM pre_admission_form where client_id='”.$_SESSION[‘client_id’].“'”);
I need to display that pre_admission_number generated by the inserted query, how can i do it. please send me reply.Its urgent…
i have to do search from multiple tables like student (student info. ), master( client_id, class, section), student_sec (secondary info of student )
i have written teh query as follows,
SELECT st.sno sss,st.name names,mt.class cl,mt.section sec FROM student st, master mt,student_sec_info st_sec WHERE st.master_sno=mt.sno and st.sno=st_sec.st_sno and mt.client_id=‘27’ OR st.name LIKE ‘Abhi%’ OR st.p_mobile LIKE ‘Abhi%’ OR st.email LIKE ‘Abhi%’ OR st_sec.f_name LIKE ‘Abhi%’ OR st_sec.f_name LIKE ‘Abhi%’ OR st_sec.m_occ LIKE ‘Abhi%’ OR st_sec.caste LIKE ‘Abhi%’ OR st_sec.subcaste LIKE 'Abhi%'OR st_sec.p_add LIKE ‘Abhi%’ OR st_sec.c_add LIKE ‘Abhi%’ LIMIT 20
Thru the above query, iam getting other clients students also…
pls send the exact wuery for search ( having more than 5 like operators, 3 joining tables, one exact condition like client in my query…)
SELECT st.sno sss,st.name names,mt.class cl,mt.section sec
FROM student st, master mt,student_sec_info st_sec
WHERE st.master_sno=mt.sno AND
st.sno=st_sec.st_sno AND
mt.client_id=‘27’ OR
st.name LIKE ‘Abhi%’ OR
st.p_mobile LIKE ‘Abhi%’ OR
st.email LIKE ‘Abhi%’ OR
st_sec.f_name LIKE ‘Abhi%’ OR
st_sec.f_name LIKE ‘Abhi%’ OR
st_sec.m_occ LIKE ‘Abhi%’ OR
st_sec.caste LIKE ‘Abhi%’ OR
st_sec.subcaste LIKE 'Abhi%'OR
st_sec.p_add LIKE ‘Abhi%’ OR
st_sec.c_add LIKE ‘Abhi%’
LIMIT 20