MySQL 5.7

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
select version();


version()


5.7.27


CREATE TABLE Table1
    (`id` int, `name` varchar(4), `address` varchar(7), `car_id` varchar(4))
;
    
INSERT INTO Table1
    (`id`, `name`, `address`, `car_id`)
VALUES
    (1, 'Alex', 'US', NULL),
    (2, 'Jaso', 'Canad a', '1'),
    (3, 'Paso', 'Canad a', NULL),
    (4, 'Paso', 'danad a', NULL)
;


CREATE TABLE Table2
    (`car_id` int, `color` varchar(7))
;
    
INSERT INTO Table2
    (`car_id`, `color`)
VALUES
    (1, 'red'),
    (2, 'blue'),
    (3, 'green') ,
    (4, 'purple')
;
Update Table1 t1a inner Join
(Select tab1.id, tab2.car_id From
(Select `id`,
          @curRank := @curRank + 1 AS rank
    FROM Table1, (SELECT @curRank := 0) r
    WHERE car_id IS NULL ) tab1 
    inner join
   (Select car_id,
          @curRank2 := @curRank2 + 1 AS rank
      From Table2 , (SELECT @curRank2 := 0) r Where car_id NOT IN 
     (
     Select Car_id From
      (Select GROUP_CONCAT(t1.car_id) car_id FROM Table1 as t1) t1e 
         WHERE car_id IS NOT NULL GROUP BY car_id
      
      ) 
    ) tab2 ON   tab1.rank = tab2.rank) t2a
    on t1a.id = t2a.id
    SET t1a.car_id = t2a.car_ID
    ;
Select `id`,
          @curRank := @curRank + 1 AS rank
    FROM Table1, (SELECT @curRank := 0) r
    WHERE car_id IS NULL;
Select car_id,
          @curRank := @curRank + 1 AS rank
      From Table2 , (SELECT @curRank := 0) r Where car_id NOT IN 
     (
     Select Car_id From
      (Select GROUP_CONCAT(t1.car_id) car_id FROM Table1 as t1) t1e 
         WHERE car_id IS NOT NULL GROUP BY car_id
      
      ) 


car_id
rank


1
1

3
2

4
3


#Update Table1 T1a
#   Set T1a.car_id = 
#   (
#   Select car_id From Table2 Where car_id NOT IN 
#     (
#     Select Car_id From
#      (Select GROUP_CONCAT(t1.car_id) car_id FROM Table1 as t1) t1e 
#         WHERE car_id IS NOT NULL GROUP BY car_id
#      
      ) 
#      
#     ORDER BY RAND( )
#    LIMIT 1 
#   ) 
#   WHERE T1a.car_id IS NULL;
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ') 
#      
#     ORDER BY RAND( )
#    LIMIT 1 
#   ) 
#   WHERE T1a.car_id IS N' at line 10

Select * FROM Table1;


id
name
address
car_id


1
Alex
US
2

2
Jaso
Canad a
1

3
Paso
Canad a
3

4
Paso
danad a
4