How to update rows using nested queries

Question

I have a table where the date of birth is 0000-00-00 where the format is YYYY-MM-DD. I have given the task to update these date to real DOB.

The DOB can only be extracted from the Identification Number which looks like 950313094353 (Not a real IC number) and IC is created like YYMMDDSNRAND where SN is state number e.g 09 and RAND is just 4 random numbers e.g 4353.

From the IC number, I can get the date of birth by substring the first 6 digits.

The table

student_st
IC_NO         D_BIRTH     COUNTRY
------------  ----------  --------
940525053455  0000-00-00  MALAYSIA

The datatype of IC_NO is varchar, D_BIRTH is date and COUNTRY is varchar

My code that I tried is

UPDATE `student_st`
SET `D_BIRTH` =  CONCAT('19', (SELECT SUBSTR(`IC_NO`, 1, 2) FROM `student_st` WHERE `COUNTRY`='MALAYSIA'),
                 '-', (SELECT SUBSTR(`IC_NO`, 3, 2) FROM `student_st` WHERE `COUNTRY`='MALAYSIA'),
                 '-', (SELECT SUBSTR(`IC_NO`, 5, 2) FROM `student_st` WHERE `COUNTRY`='MALAYSIA')) 
WHERE `COUNTRY`='MALAYSIA' AND DATE(`D_BIRTH`)='0000-00-00'

This is the error that I'm getting

Error in query (1292): Truncated incorrect date value: '0000-00-00'

I don't know why the error is stating this.

From my code, the result that I expected is 19YY-MM-DD where YY, MM and DD obtained from substring the IC number.

'MALAYSIA' Backticks should be quotes.please review https://stackoverflow.com/questions/11321491/when-to-use-single-quotes-double-quotes-and-back-ticks-in-mysql — P.Salmon, Apr 03 '19 at 08:35
Ok thanks for the clarification. Now I'm getting the error `Error in query (1292): Incorrect date value: '0000-00-00' for column 'D_BIRTH' at row 1`. I changed the backtick in D_BIRTH too — nazhannasir, Apr 03 '19 at 08:42
The datatype for D_BIRTH is date NULL. I tried DATE(D_BIRTH) = 0000-00-00 but it says `Error in query (1292): Truncated incorrect date value: '0000-00-00'` — nazhannasir, Apr 03 '19 at 08:55

score 0 · Accepted Answer · answered Apr 03 '19 at 09:07

You don't need all those selects in sub queries an update is a row by row update.

drop table if exists t;
create table t
(IC_NO  varchar(20),       D_BIRTH  date, country varchar(20));
insert into t values
(940525053455 , '0000-00-00','malasia'),
(960525053455 , '1995-05-25','malasia'),
(940525053455 , '0000-00-00','aa');

update t
        set d_birth = str_to_date(concat('19',SUBSTR(`IC_NO`, 1, 2),SUBSTR(`IC_NO`, 3, 2) ,SUBSTR(`IC_NO`, 5, 2)),'%Y%m%d')
where country = 'malasia' and d_birth = '0000-00-00'
;

select * from t;

+--------------+------------+---------+
| IC_NO        | D_BIRTH    | country |
+--------------+------------+---------+
| 940525053455 | 1994-05-25 | malasia |
| 960525053455 | 1995-05-25 | malasia |
| 940525053455 | 0000-00-00 | aa      |
+--------------+------------+---------+
3 rows in set (0.00 sec)

I followed your code at the UPDATE part and I'm getting this error `Error in query (1292): Incorrect date value: '0000-00-00' for column 'D_BIRTH' at row 1`. I tried DATE(D_BIRTH) = 0000-00-00 but then it says `Error in query (1292): Truncated incorrect date value: '0000-00-00'` — nazhannasir, Apr 03 '19 at 09:19
nvm it is just my SQL version preventing this. THANK YOU SO MUCH :) — nazhannasir, Apr 03 '19 at 09:27

How to update rows using nested queries

1 Answers1