SitePoint Sponsor

User Tag List

Results 1 to 8 of 8
  1. #1
    SitePoint Guru
    Join Date
    Sep 2008
    Posts
    977
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    insert statement to use select

    Hi,

    I am inserting to a single table as shown below.

    Code mysql:
    insert into image_gallery_headers 
      ( business_id, gallery_category, gallery_header )
      values( ?,?,? )
      on duplicate key 
         update business_id = ?
       , gallery_category = ?
       , gallery_header = ?

    The ? just replaces each actual variable.

    Now, I want to change that statement, to insert with another extra col, sequence_number. the value to be inserted needs to be queried from the same table and be the highest value + 1 for this category

    Where should the select statement go?

    Code mysql:
    insert into image_gallery_headers 
      ( business_id, gallery_category, gallery_header, sequence_number )
      values( ?, ?, ?, ig.sequence_number )
      on duplicate key 
         update business_id = ?
       , gallery_category = ?
       , gallery_header = ?  
       , sequence_number = ig.sequence_number  
    where ig.sequence_number = (select max(sequence_number) +1
                                          from image_galleries as ig
                                          where business_id = ?
                                          and gallery_category = ?
                                          and gallery_header=?
                                            )

    any help much appreciated

    bazz

  2. #2
    SitePoint Guru
    Join Date
    Sep 2008
    Posts
    977
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    oh, I am stupid...

    this is what I got to work about 30 secs after posting.

    Code MySQL:
    insert into image_gallery_headers 
      ( business_id, gallery_category, gallery_header, sequence_number )
      values( ?, ?, ?, sequence_number )
      on duplicate key 
         update business_id = ?
       , gallery_category = ?
       , gallery_header = ?  
       , sequence_number = (select max(sequence_number) +1
                                          from image_gallery_headers as igh
                                          where business_id = ?
                                          and gallery_category = ?
                                          and gallery_header=?
                                            )

  3. #3
    SitePoint Guru
    Join Date
    Sep 2008
    Posts
    977
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    actually, firefox lied to me by not throwing an error

    so I have this currently.

    Code MySQL:
    insert into image_gallery_headers 
      ( business_id, gallery_category, gallery_header, sequence_number )
      values( 477, 'main photo album', 'this', (select max(sequence_number)+1
                       from image_gallery_headers
    		  where business_id = 477
    		  and gallery_category = 'main photo album'
    		  )
      )
      on duplicate key 
         update business_id = 477
       , gallery_category = 'main photo album'
       , gallery_header = 'this'  
       , sequence_number = (select max(igh.sequence_number)
                               from image_gallery_headers as igh
                              where business_id = 477
                                and gallery_category = 'main photo album')

    the error is:

    You can't specify target table 'image_gallery_headers' for update in FROM clause at create_image_gallery line 171.

    I don't know what that means. Does it mean that I can't 'on duplicate update' a table which I am querying? what would the solution be please.

    bazz
    Last edited by IBazz; Oct 30, 2010 at 17:26. Reason: wrong query posted. now corrected

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,262
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    that doesn't make a lot of sense

    which is the key that gets duplicated?

    also, why aren't you using an auto_increment instead of the horrid MAX()+1 idea?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Guru
    Join Date
    Sep 2008
    Posts
    977
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    ok, sorry for the nonsense.

    four cols to be inserted into or updated. (maybe I should just use insert ignore?)

    the sequence number cannot be auto_increment. it is a sequence number to control the order in which images display on the web page. when inserting a new record, I want the sequence number to be 1 more than the maximum sequence_number already inserted as a default sequence_number, if you like.

    This ensures that new images won't interfere with a sequence already set up.


    I have done a query which just uses insert ignore. how would I add on to that, the on duplicate update section, so as to prevent errors, if the page is refreshed or the submit button clicked twice.

    Code MySQL:
    insert ignore into image_gallery_headers 
      ( business_id, gallery_category, gallery_header, sequence_number )
      select '477', 'main photo album', 'Test', max(sequence_number)+1
        from image_gallery_headers
      where business_id = 477
         and gallery_category = 'main photo album'
    bazz

  6. #6
    SitePoint Guru
    Join Date
    Sep 2008
    Posts
    977
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    btw, if max()+1 is a MySQL only thing, would you mind telling me the standard SQL method, please.

    bazz

  7. #7
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,262
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by IBazz View Post
    it is a sequence number to control the order in which images display on the web page.
    my approach to that problem is to have a default sequence number, so that unless i give the entry a specific sequence number, it'll get sorted in with all the other entries with the same (default) sequence number, and then, in any SELECT where i have an ORDER BY on sequence, i also have date_added as the secondary sort column, so this MAX()+1 nonsense (which is not peculiar to mysql) can be avoided

    Quote Originally Posted by IBazz View Post
    how would I add on to that, the on duplicate update section
    the first thing you have to do is figure out which key you expect will be duplicated

    can you explain?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  8. #8
    SitePoint Guru
    Join Date
    Sep 2008
    Posts
    977
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks rudy.

    I'll think this through some more before posting again.

    bazz


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
  •