How to extract the year of a DATE and insert it in a new column?

Question

I have a column in my table which contains DATEs and I need to keep only the YEAR (=> in a NUMBER or INT type)

My idea was to add a column and try something like this :

UPDATE myTable SET newColumn=(
select to_number(to_char(oldColumn.year, 'yyyy')) from myTable)

But I get this error :

single-row subquery returns more than one row

Could you help?!?

Answer 1

You need to change your query to:

UPDATE myTable 
SET newColumn=to_number(to_char(oldColumn.year, 'yyyy'))

This SELECT statement will return all of the rows in the table, from column oldColumn.year, which generates your "single-row subquery returns more than one row" error.

(select to_number(to_char(oldColumn.year, 'yyyy')) from myTable)