如何在值为空时更新，否则在下一行更新

I have a task assignment system and I want to assign people to certain tasks. The table is laid out like this:

+---------+---------+-------------+-------------+-------------+
|Task name| person1 | person2     | person3     | person4     |
+---------+---------+-------------+-------------+-------------+

If a task has already been assigned to for example 2 people I want the update function to populate person 3. So it basically checks if the value is empty, if so enter data, otherwise check if value in next column is empty and place it there, and continue this for all columns.

I have already tried this but this outputs an error:

UPDATE data SET IF(person1 IS NULL, person1, 
                  IF(person2 IS NULL,person2,
                    IF(person3 IS NULL, person3,
                      IF(person4 IS NULL, person4,
                        IF(person5 IS NULL, person5, person6)))))
                 ='$person_name' WHERE id=$id

This outputs the following error:

You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'IF(person1 IS NULL, person1, IF(person2 IS NULL, person2, IF(person3 IS NULL, back' at line 1

But as far as my knowledge goes this is a general error.

see sample code below. what i did to update the tasks table was to take the table inner join it with itself in order to get the old values.

the reason we need to join the table with itself is because one table will hold the columns with updated values, while the other will not change its values. this way we can check if a column was updated.

make sure the table that you are updating has a unique id to join them by so only one record is joined with itself.

the rest is updating the persons columns with case statements. if a person column is empty and you have not updated any previous persons column, then you can go ahead and update it.

I have included my test schema which i tried to simulate to yours, along with the update query.

here is where i tested it: https://www.db-fiddle.com/f/bxSWZhj7NXfm7J5pCQ5Kw1/0

create table my_tasks(
task_name VARCHAR(100),
 person1 VARCHAR(100),
  person2 VARCHAR(100),
  person3 VARCHAR(100),
  person4 VARCHAR(100)

);

insert into my_tasks (task_name,person1,person2,person3,person4) values('test',NULL,'fsd','fsd','fsdfs'),('test2','fsdfs',NULL,NULL,NULL),('test3','ffdsfs',NULL,'fsdfsf',NULL);

UPDATE my_tasks INNER JOIN my_tasks as tasks2 on my_tasks.task_name=tasks2.task_name
  SET my_tasks.person1= CASE 
    WHEN my_tasks.person1 IS NULL THEN 'person 1 update'
    ELSE my_tasks.person1
  END,
  my_tasks.person2 = CASE 
    WHEN  my_tasks.person2 IS NULL AND my_tasks.person1=tasks2.person1
    THEN 'person 2 update'
    ELSE my_tasks.person2
   END,
   my_tasks.person3 = CASE
        WHEN my_tasks.person3 IS NULL AND my_tasks.person2=tasks2.person2 AND my_tasks.person1=tasks2.person1
        THEN 'person 3 update'
        ELSE my_tasks.person3
   END,
   my_tasks.person4 = CASE
    WHEN my_tasks.person4 IS NULL AND my_tasks.person3=tasks2.person3 AND my_tasks.person2=tasks2.person2 AND my_tasks.person1=tasks2.person1
    THEN 'person 4 update'
    ELSE my_tasks.person4
   END

WHERE my_tasks.task_name='test3';

Your best option, long term, is a table redesign:

Tasks: TaskId (PK, auto inc), TaskName (varchar)
Task_Assignees: TaskId (references Tasks.TaskId), Person (varchar)

or better

Tasks: TaskId (PK, auto inc), TaskName (varchar)
Workers: WorkerId (PK, auto inc), WorkerName (varchar)
Task_Workers: TaskId (references Tasks.TaskId), WorkerId (references Workers.WorkerId)

In either the above, you simply remove rows to "unassign" people, and just insert rows to assign them. If the order actually matters it might get the tiniest bit more complicated, but not by much.

This should work for what you need:

UPDATE `data`
   SET person4 = IF(person4 IS NULL AND person3 IS NOT NULL, 'value', person4),
       person3 = IF(person3 IS NULL AND person2 IS NOT NULL, 'value', person3),
       person2 = IF(person2 IS NULL AND person1 IS NOT NULL, 'value', person2),
       person1 = IF(person1 IS NULL, 'value', person1)
 WHERE id = $id;

EDIT: The reason you want to do your SETs in reverse order like this is that MySQL will do each of the SETs individually and those results will be used in the IF() condition for the next SET. So if you SET person2 to a value then check person3 it will see that person2 was already filled in and SET person3, then the check for person4 would see that person3 had a value and set person4 as well. Checking in reverse order avoids this entirely.