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;