SitePoint Sponsor

User Tag List

Page 1 of 2 12 LastLast
Results 1 to 25 of 27
  1. #1
    SitePoint Addict CommanderZ's Avatar
    Join Date
    Apr 2006
    Location
    Czech Republic
    Posts
    236
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Need to use same table in subquery as in insert query

    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

  2. #2
    . shoooo... silver trophy logic_earth's Avatar
    Join Date
    Oct 2005
    Location
    CA
    Posts
    9,013
    Mentioned
    8 Post(s)
    Tagged
    0 Thread(s)
    As you found out, you cannot.
    Logic without the fatal effects.
    All code snippets are licensed under WTFPL.


  3. #3
    SitePoint Addict CommanderZ's Avatar
    Join Date
    Apr 2006
    Location
    Czech Republic
    Posts
    236
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks for quick reply. So double query is the only way how to do this?

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,250
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    try it like this --
    Code:
    INSERT INTO testtab (value)
     SELECT MAX(value)+1 FROM testtab
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Addict CommanderZ's Avatar
    Join Date
    Apr 2006
    Location
    Czech Republic
    Posts
    236
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,250
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    well, value happens to be your column name, right? the syntax is
    Quote Originally Posted by daManual
    INSERT [LOW_PRIORITY] [IGNORE] [INTO] tbl_name [(column_list)]
    SELECT ...
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    SitePoint Addict CommanderZ's Avatar
    Join Date
    Apr 2006
    Location
    Czech Republic
    Posts
    236
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Okay, thanks, I will have to have a look at it. It seems interesting to me

  8. #8
    SitePoint Enthusiast
    Join Date
    Dec 2009
    Posts
    53
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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. #9
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,250
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    try this --
    Code:
    INSERT 
      INTO testtab
         ( value
         , name ) 
    SELECT MAX(value)+1
         , 'sunil'
      FROM testtab
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  10. #10
    SitePoint Enthusiast
    Join Date
    Dec 2009
    Posts
    53
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    HI
    Thanks for your immediate reply.
    Realy Iam very happy.
    I will contact this site , when i get any queries.

  11. #11
    SitePoint Enthusiast
    Join Date
    Dec 2009
    Posts
    53
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    HI
    Thanks for your immediate reply.
    Realy Iam very happy.
    I will contact this site , when i get any queries.

  12. #12
    SitePoint Enthusiast
    Join Date
    Dec 2009
    Posts
    53
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.............

  13. #13
    SitePoint Enthusiast
    Join Date
    Dec 2009
    Posts
    53
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.

  14. #14
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,250
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by nagasunil View Post
    ... may be two users get same pre_adm_number.
    no, not possible
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  15. #15
    SitePoint Enthusiast
    Join Date
    Dec 2009
    Posts
    53
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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

  16. #16
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,250
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by nagasunil View Post
    how can i solve this problem?
    maybe you could declare some unique columnssssssssssssssssss
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  17. #17
    SitePoint Enthusiast
    Join Date
    Dec 2009
    Posts
    53
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    pls send example for the below query,

    Add unique constraint for multiple columns mysql

  18. #18
    SitePoint Wizard
    Join Date
    Mar 2002
    Location
    Bristol, UK
    Posts
    2,240
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    CREATE UNIQUE INDEX index_name ON table_name (column1, column2, column3 ... );

  19. #19
    SitePoint Enthusiast
    Join Date
    Dec 2009
    Posts
    53
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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. #20
    SitePoint Enthusiast
    Join Date
    Dec 2009
    Posts
    53
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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

  21. #21
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,250
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    you are mixing ANDs and ORs -- use parentheses to ensure you get the right combinations
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  22. #22
    SitePoint Enthusiast
    Join Date
    Dec 2009
    Posts
    53
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.

  23. #23
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,250
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  24. #24
    SitePoint Enthusiast
    Join Date
    Dec 2009
    Posts
    53
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    iam sorry for that, actually i tried to create new thread once, iam unable to get that, thats y iam doing here

  25. #25
    SitePoint Enthusiast
    Join Date
    Dec 2009
    Posts
    53
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    how to post new thread, kindly send me link. iam very sorry for asking this silly question.


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •