SQL Assignment query I have 2 databases - one with customers' data and the second one with discount codes. I would like to assign code to each customer. The code can be assigned only once, so I would like to mark the code as 'used' once it is assigned. The codes should be assigned only to those customers that don't have a code assigned. Unused code can be assigned to any customer.
CUSTOMER_DATABASE:




CUSTOMER ID
CUSTOMER CODE




1
6f73js


2



...
...




DATA_DISCOUNT_CODES:




ID
CODE
STATUS




1
6f73js
USED


2
kdw45s



...
...
...




Could you please help me write SQL query for this, using MySQL 8.0 syntax?
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&lt;&gt;fiddle here