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.
SELECT GET_LOCK('image_$item_id', 20). Read up on GET_LOCK() and RELEASE_LOCK(). Make sure to check the return value from GET_LOCK() to ensure it did not time out.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;
