I have a table with the fields as follow:

Using SQL, I wanted to populate the blanks with the previous record or the next record of the same employee.

For example, for department = '', class = 'ZG', I want to populate it with the previous available record. In the above case would be Finance for emp001 and Marketing for the emp002.

For department = '', class = 'ZH', I want to populate the blank with the next available record. In the above case, it would be Research for emp001 and Innovation for emp002.

I was thinking of a few possibilities like using of case when but I am lost as to how to get the specific previous or next record for that particular employee.

Is it possible to populate these blanks with the above conditions using SQL so that my output is as below?

CodePudding user response：

Please try this out, this is a solution for mysql

UPDATE mytable
SET number = (@n := COALESCE(department, @n));

Explanation of the above query:

@n is a MySQL user variable. It starts out NULL, and is assigned a value on each row as the UPDATE runs through rows.

COALESCE() returns first non null value

CodePudding user response：

You can solve this by using a simple if condition instead of using a switch case.

To get the previous row value, you can use a variable & the assignment operator ( := ).

• This assigns a value to a variable as well as returns that value.
• The value on the right is assigned to the variable on left.

Logic

• If the department column is empty, get the value from @prevVal variable.
• If the department column is not empty, simply assign the column's value to the @prevVal variable. So that in the next row, if the department is empty, it will return the previouly stored value from @prevVal variable. i.e. previous row value.

So for your problem the query would be.

SELECT
    pk,
    id,
    start_date,
    class,
    if(department = '', @prevVal, @prevVal:=department) as department
FROM employees;