insert statement to use select

[IBazz]

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

[IBazz]

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

[IBazz]

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

[r937]

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?

[IBazz]

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

[IBazz]

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

bazz

[r937]

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

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?

[IBazz]

Thanks rudy.

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

bazz