Insert statement to use select

Hi,

I am inserting to a single table as shown below.


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?


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

oh, I am stupid…

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


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=?
                                        )

actually, firefox lied to me by not throwing an error

so I have this currently.


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

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?

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.


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

btw, if max()+1 is a MySQL only thing, would you mind telling me the standard SQL method, please.

bazz

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

the first thing you have to do is figure out which key you expect will be duplicated

can you explain?

Thanks rudy.

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

bazz