SQL Data entry - finding sequence to enter info

Question

I've been working on a DBO that requires a new code going through.

I have to manually add a specific code to my requirements in a column that has thousands of rows.

The issue is, some are already populated and I need to add them into the next available empty column.

its currently in this format:

I need to add Code Z987 to the first NULL of each ID

Answer 1

First, whoever designed this data needs to read up on database normalization, as this design violates the most basic principles. The best solution would be to redesign the data to follow good design practices.

However, if that is not an option, you will need to write an UPDATE statement that updates every column, but conditionally chooses either the current or new value as the value to be assigned. The condition would be based on which columns are already set. To avoid duplication of logic, you can use a CROSS APPLY to identify which column is to be updated. That can then be used to feed CASE expressions in the SET logic.

The result would be something like:

UPDATE T
SET
    ColumnA = CASE WHEN C.ColumnSelect = 1 THEN 'NewValue' ELSE ColumnA END,    
    ...
    ColumnF = CASE WHEN C.ColumnSelect = 6 THEN 'NewValue' ELSE ColumnF END
FROM YourTable T
CROSS APPLY (
    SELECT CASE
        WHEN ColumnA IS NULL THEN 1
        ...
        WHEN ColumnF IS NULL THEN 6
        ELSE 0
        END AS ColumnSelect
) C