adding foreign key from other table based on column values of first table

I am new to mysql

I have two Tables in my database.

id_details_table

CREATE TABLE `id_details_table` (
  `ID` int(11) NOT NULL,
  `CNIC` varchar(255) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

sample record

INSERT INTO `id_details_table` (`ID`, `CNIC`) VALUES
(1, '3230328119795'),
(2, '4200004873681'),
(3, '4230188867895'),
(4, '3740566124323'),
(5, '4220191179125');

mobiles_sim_details

>  CREATE TABLE `mobiles_sim_details` (  
`Mobile` double DEFAULT NULL, 
> `CNIC` varchar(255) DEFAULT NULL,   `id_cnic` int(11) DEFAULT NULL )
> ENGINE=InnoDB DEFAULT CHARSET=latin1;

sample Record for table

INSERT INTO `mobiles_sim_details` (`Mobile`, `CNIC`, `id_cnic`) VALUES
(3000651082, '3230328119795', 0),
(3040877459, '4200004873681', 0),
(3013329415, '4230188867895', 0),
(3028590340, '3740566124323', 0),
(3000720166, '4220191179125', 0);

2 columns CNIC are present in both tables Now What I want is, where there is 0 in the 2nd table I want it to be auto filled with the relevant ID from table 1 based on the data of CNIC from 2nd table.

I have tried this query but it didn't worked

UPDATE `mobiles_sim_details` SET `id_cnic`='[value-3]' WHERE (SELECT id FROM id_details_table WHERE CNIC = mobiles_sim_details.CNIC);

How can i Acheive this ?

CodePudding user response：

UPDATE mobiles_sim_details t1
JOIN id_details_table t2 USING (CNIC)
SET t1.id_cnic = t2.id