E
This is a bit tricky as you have to generate ROW_NUMBERs for both tables to join them.
IF you want only to include certain customers you need further criteria than CUSTOMER CODE IS NULL.
And a word of advise, don't use spaces in your column names, you need to encapsulate them always with backticks, that makes much more work, than having column names that lack spaces.
CREATE TABLE CUSTOMER_DATABASE (
`CUSTOMER ID` INTEGER,
`CUSTOMER CODE` VARCHAR(6)
);
INSERT INTO CUSTOMER_DATABASE
(CUSTOMER ID, CUSTOMER CODE)
VALUES
('1', '6f73js'),
('2', NULL),
('3', NULL);
CREATE TABLE DATA_DISCOUNT_CODES (
`ID` INTEGER,
`CODE` VARCHAR(6),
`STATUS` VARCHAR(4)
);
INSERT INTO DATA_DISCOUNT_CODES
(ID, CODE, STATUS)
VALUES
('1', '6f73js', 'USED'),
('2', 'kdw45s', NULL),
('3', 'and43d', NULL);
UPDATE CUSTOMER_DATABASE T1
INNER JOIN (SELECT `CUSTOMER ID`
, ROW_NUMBER() OVER () rown
FROM CUSTOMER_DATABASE WHERE `CUSTOMER CODE` IS NULL) T2 ON T1.`CUSTOMER ID` = T2.`CUSTOMER ID`
INNER JOIN (SELECT `CODE`
, ROW_NUMBER() OVER () rown
FROM DATA_DISCOUNT_CODES
WHERE `STATUS` IS NULL) T3 ON T2.rown = T3.rown
INNER JOIN DATA_DISCOUNT_CODES T4 ON T3.`CODE` = T4.`CODE`
SET T1.`CUSTOMER CODE` = T4.`CODE`,
T4.`STATUS` = 'USED'
✓
SELECT * FROM CUSTOMER_DATABASE;
CUSTOMER ID | CUSTOMER CODE
----------: | :------------
1 | 6f73js
2 | kdw45s
3 | and43d
SELECT * FROM DATA_DISCOUNT_CODES
ID | CODE | STATUS
-: | :----- | :-----
1 | 6f73js | USED
2 | kdw45s | USED
3 | and43d | USED
db<>fiddle here
