sql - mysql loop through columns until null is found, then insert and break

Question

I am looking for a mysql query that does the following....

I have a mySql table called 'devices' and a row in this table called 'row'. The 'row' has 15 columns that are initially null. I want to start on column 1 and check if null. If null, insert something into this column for 'row' and break the loop. if not null, move onto column 2 and repeat. What is the best way of achieving this?

Schema below:

-- Table structure for table devices

CREATE TABLE `devices` (
  `uid` varchar(25) COLLATE utf8_unicode_ci NOT NULL,
  `d1` varchar(20) COLLATE utf8_unicode_ci DEFAULT NULL,
  `d2` varchar(20) COLLATE utf8_unicode_ci DEFAULT NULL,
  `d3` varchar(20) COLLATE utf8_unicode_ci DEFAULT NULL,
  `d4` varchar(20) COLLATE utf8_unicode_ci DEFAULT NULL,
  `d5` varchar(20) COLLATE utf8_unicode_ci DEFAULT NULL,
  `d6` varchar(20) COLLATE utf8_unicode_ci DEFAULT NULL,
  `d7` varchar(20) COLLATE utf8_unicode_ci DEFAULT NULL,
  `d8` varchar(20) COLLATE utf8_unicode_ci DEFAULT NULL,
  `d9` varchar(20) COLLATE utf8_unicode_ci DEFAULT NULL,
  `d10` varchar(20) COLLATE utf8_unicode_ci DEFAULT NULL,
  `d11` varchar(20) COLLATE utf8_unicode_ci DEFAULT NULL,
  `d12` varchar(20) COLLATE utf8_unicode_ci DEFAULT NULL,
  `d13` varchar(20) COLLATE utf8_unicode_ci DEFAULT NULL,
  `d14` varchar(20) COLLATE utf8_unicode_ci DEFAULT NULL,
  `d15` varchar(20) COLLATE utf8_unicode_ci DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

-- -- Dumping data for table devices

INSERT INTO `devices` (`uid`, `d1`, `d2`, `d3`, `d4`, `d5`, `d6`, `d7`, `d8`, `d9`, `d10`, `d11`, `d12`, `d13`, `d14`, `d15`) VALUES
('t', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL),
('tt', '0202020', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL);
COMMIT;

question from:https://stackoverflow.com/questions/65835478/mysql-loop-through-columns-until-null-is-found-then-insert-and-break

Answer 1

First, let me say that I hope someone has a cleaner solution to this problem, but here's how I would approach it.

I would declare a CURSOR to iterate through the rows of the devices table, unless you are always going to be dealing with just one row. If you do not know how to use cursors, please read here.

Declare a variable for each column in your table and select the row data into those variables. Then I would use a CASE statement, like this:

CASE
    WHEN d1 IS NULL THEN UPDATE `devices` SET `d1` = 'value'; RETURN;
    WHEN d2 IS NULL THEN UPDATE `devices` SET `d2` = 'value'; RETURN; 
    (etc.)
    WHEN d15 IS NULL THEN UPDATE `devices` SET `d15` = 'value'; RETURN;
    ELSE RETURN;
END CASE

This solution is rather clunky, but it might be worth a try.