Make MIN(id) the largest MAX(id) + 1 in a table

1

Hi guys I have a table that I want to use to queue singers.

the table is called queue what I need to happen when I press a button on my page is to go through the table q find the smallest id and update to 1 greater the the largest id but I can't seem to get the syntax right ...tried many ways but am stuck. The id field auto increments.

thanks in advance for any advice

   UPDATE `queue`
   SET `id` =  MAX(id) + 1
   WHERE `id` = MIN(id);
2

not sure if doing what you are thinking is really queuing artists in the proper way?

create table #queue(id int, artistname varchar(50))
insert into #queue
select object_id, name from sys.objects where object_id between 1 and 10

select * From #queue order by id

UPDATE tgt
SET tgt.id =  big_kauna.tuna
from #queue tgt
cross apply (select MAX(id) + 1 as tuna from #queue) big_kauna
   
   WHERE id in (select  MIN(id) from #queue);

select * From #queue order by id asc

drop table #queue
3

Hi thank you for the reply but that all seems quite confusing for what I was trying to accomplish Ive looked over the code but know very little sql....the update tgt ... I dont understand what that is. I will look into it further Ty

4

i just added a sample data for you to see how to do it and test it. but in your case you would do the following

UPDATE tgt
SET tgt.id =  src.plusone 
from queue tgt
cross apply (select MAX(id) + 1 as plusone from queue) src   
   WHERE id in (select  MIN(id) from queue);
5

Thank you again for your response but still a little confused with all this tgt stuff how does that come into play and will this work with mySQL I tried your code in mySQL Workbench but so far it doesn't like the FROM.

6

I am not sure if it would work in MySQL. This is for Microsoft SQL Server.