Update age SQL Oracle

Question

I have a table called DOC_ADMISSAO2 where I have the date of birthday (datatype is date) and another date (datatype is date) and I want the age.

I used this query:

update DOC_ADMISSAO2 
set IDADE = (SELECT EXTRACT(YEAR FROM DATACRIACAO) - EXTRACT(YEAR FROM DATAN)
             from DOC_ADMISSAO2));

But it showed me this error:

Error starting at line : 2 in command - update DOC_ADMISSAO2 set IDADE = (SELECT EXTRACT(YEAR FROM DATACRIACAO) - EXTRACT(YEAR FROM DATAN) from DOC_ADMISSAO2) Error report - SQL Error: ORA-01427: subconsulta de linha única devolve mais que uma linha 01427. 00000 - "single-row subquery returns more than one row" *Cause:
*Action:

Help? I'm kinda new at this.

Data redundancy. Drop the column IDADE, because it will contain inconsistent data sooner or later. Create a view instead, which does the required calculation - and will always return consistent data! — jarlh
#1: This calculation doesn't return an age a human being would expect. #2: As the actual age might change every day this should be calculated and not stored — dnoeth
I would suggest that you use months_between() / 12. It is more accurate than a difference of years. — Gordon Linoff
@GordonLinoff: But don't forget to CAST it as an INT or apply FLOOR, an age 34.7536882653357754697 looks strange :) — dnoeth

Aleksej Aleksej · Accepted Answer · 2016-04-05T14:11:17

You do not need a nested query to update your rows:

update DOC_ADMISSAO2
set IDADE = EXTRACT(YEAR FROM DATACRIACAO) - EXTRACT(YEAR FROM DATAN)

Of course this computes only the difference between years, so if you have, for example, DATACRIACAO = 01/01/2016 and DATAN= 31/12/2015 you will have an age of 1 year. Besides, as said in the comments, it makes not so much sense to store an age in a table, given that it can change everyday.

So, this could solve your query question, but it would be better to think of a different table design and evaluate the age in some different way.

Update age SQL Oracle

1 Answers