Insert row with value depending on IF statement (in single query)

Let's suppose we have table with images...

  image_id
  item_id
  main - flag-field, saying that image_id is main image for item with item_id

Is it possible to insert 开发者_开发问答row into this table and determine main value on fly? If there are no any images for item_id or there are no any images for items_id with main=1 - we should set main=1 for inserted row. Otherwise main=0;

Is it possible to make in a single SQL query?

Thank you. PS: Sorry for my English. :)

Sorry, no. Not in one query.

You will need to count the rows first, in your php application, then set main depending on that.

You could do it in a trigger though if it was really important to do it in the database.

Try smth like that... now input values stored in variables, but it can be simple table...

create table #T (image_id int, item_id int, main int)
declare @new_image_id int, @new_item_id int

insert #T values (0,1,0)
insert #T values (0,1,0)
insert #T values (0,1,0)
insert #T values (0,2,0)
insert #T values (1,2,0)
insert #T values (1,2,0)

/*first example - no any images for item_id*/

select @new_image_id = 123, @new_item_id = 1

insert #T 
select @new_image_id, @new_item_id,
    CASE
        WHEN NOT EXISTS (select 1 from #T where item_id = @new_item_id and @new_image_id != 0)
            THEN 1
        WHEN NOT EXISTS (select 1 from #T where item_id = @new_item_id and main = 1)
            THEN 1
        ELSE 0
    END

/*second example - no any images for items_id with main=1*/

select @new_image_id = 234, @new_item_id = 2

insert #T 
select @new_image_id, @new_item_id,
    CASE
        WHEN NOT EXISTS (select 1 from #T where item_id = @new_item_id and @new_image_id != 0)
            THEN 1
        WHEN NOT EXISTS (select 1 from #T where item_id = @new_item_id and main = 1)
            THEN 1
        ELSE 0
    END

/*third example - new record with main = 0*/

select @new_image_id = 435, @new_item_id = 2

insert #T 
select @new_image_id, @new_item_id,
    CASE
        WHEN NOT EXISTS (select 1 from #T where item_id = @new_item_id and @new_image_id != 0)
            THEN 1
        WHEN NOT EXISTS (select 1 from #T where item_id = @new_item_id and main = 1)
            THEN 1
        ELSE 0
    END



select * from #T

Example on t-sql...

Max

If your table looks somthing like this:

CREATE TABLE images (
    image_id SERIAL,
    item_id BIGINT UNSIGNED NOT NULL,
    main TINYINT UNSIGNED NOT NULL,
    path TINYTEXT NOT NULL
)

And you want to add the picture 'example.png' to item 123, you could use:

INSERT INTO images(item_id, main, path) 
SELECT new_image.item_id, IF(images.item_id IS NULL, 1, 0), new_image.path 
FROM (SELECT 123 as item_id, 'example.png' as path) AS new_image
LEFT JOIN images USING (item_id)
LIMIT 1;