Need to use same table in subquery as in insert query

**CommanderZ** · Jan 27, 2007 07:04

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:

Code:

INSERT INTO testtab SET value=(select max(value) from testtab)+1

Mysql threw this error:

Code:

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

**logic_earth** · Jan 27, 2007 07:08

As you found out, you cannot.

**CommanderZ** · Jan 27, 2007 07:09

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

**r937** · Jan 27, 2007 07:45

try it like this --

Code:

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

**CommanderZ** · Jan 27, 2007 07:51

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

**r937** · Jan 27, 2007 07:56

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

Originally Posted by daManual

INSERT [LOW_PRIORITY] [IGNORE] [INTO] tbl_name [(column_list)]
SELECT ...

**CommanderZ** · Jan 27, 2007 08:29

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

**nagasunil** · Dec 31, 2009 00:24

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.

**r937** · Dec 31, 2009 01:45

try this --

Code:

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

**nagasunil** · Dec 31, 2009 02:19

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

**nagasunil** · Dec 31, 2009 02:20

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

**nagasunil** · Jan 2, 2010 05:16

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.............

**nagasunil** · Jan 2, 2010 05:20

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.

**r937** · Jan 2, 2010 05:38

Originally Posted by nagasunil

... may be two users get same pre_adm_number.

no, not possible

**nagasunil** · Jan 2, 2010 05:43

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

**r937** · Jan 2, 2010 05:46

Originally Posted by nagasunil

how can i solve this problem?

maybe you could declare some unique columnssssssssssssssssss

**nagasunil** · Jan 2, 2010 05:55

pls send example for the below query,

Add unique constraint for multiple columns mysql

**SJH** · Jan 2, 2010 08:41

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

**nagasunil** · Jan 18, 2010 01:11

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...

**nagasunil** · Jan 18, 2010 01:13

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

**r937** · Jan 18, 2010 04:08

you are mixing ANDs and ORs -- use parentheses to ensure you get the right combinations

**nagasunil** · Feb 1, 2010 06:19

hi i have the categories table like this

cat_id, cat_name, parent_id

root category is having '0' parent_id

and categories will comes under their parent ids.

I have to present the the whole categories in front end in <ul> list

i think you got my problem...

pls give me help.

**r937** · Feb 1, 2010 06:34

see Categories and Subcategories

and please, if you have a new question, please start a new thread, don't just keep adding on new questions to CommanderZ's thread