Need to use same table in subquery as in insert query

Databases
1

Hello,
I have this table called “testtab”:

ID | value

1 | 8
2 | 15
3 | 5

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.

Thanks for help

2

As you found out, you cannot.

3

Thanks for quick reply. So double query is the only way how to do this?

4

try it like this –

INSERT INTO testtab (value)
 SELECT MAX(value)+1 FROM testtab
5

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 :slight_smile:

6

well, value happens to be your column name, right? the syntax is

7

Okay, thanks, I will have to have a look at it. It seems interesting to me :slight_smile:

8

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.

9

try this –

INSERT 
  INTO testtab
     ( value
     , name ) 
SELECT MAX(value)+1
     , 'sunil'
  FROM testtab
10

HI
Thanks for your immediate reply.
Realy Iam very happy.
I will contact this site , when i get any queries.

11

HI
Thanks for your immediate reply.
Realy Iam very happy.
I will contact this site , when i get any queries.
:slight_smile:

12

ok nope

i have one more doubt

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

how can i solve this problem?

pls helppppppppp

13

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…

14

i know that mysql_insert_id() returns the last inserted id, And we can get pre_adm_number by that.

But the problem is "if so many users clicks at the same time, may be two users get same pre_adm_number.

15

no, not possible

16

maybe you could declare some unique columnssssssssssssssssss

17

pls send example for the below query,

Add unique constraint for multiple columns mysql

18

CREATE UNIQUE INDEX index_name ON table_name (column1, column2, column3 … );

19

hi

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…)

PLease it is urgent , Thanks in Advance…

20

Query with indentation,

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