regarding a query [message #348994] |
Thu, 18 September 2008 09:08  |
ukdas
Messages: 32 Registered: September 2008 Location: London
|
Member |

|
|
hi,
i have been trying to update some information in a table and i just stuck in a concept. the query i have given below:
1 update
2 agents
3 set
4 deactivation_date=
5 (
6 select
7 min(deactivation_date)
8 from
9 agents
10 where
11 ABS(TO_NUMBER(TO_CHAR(birth_date,'YYYY')-TO_CHAR (SYSDATE, 'YYYY')))>50
12 and
13 deactivation_date=to_date(deactivation_date, 'MM-MON-YYYY')
14 )
15 ;
at line 13, i need to get those deactivation date which are exists in the table for those who are already deactivated.
regards
ukd
|
|
|
|
|
|
Re: regarding a query [message #349014 is a reply to message #348994] |
Thu, 18 September 2008 10:02   |
cookiemonster
Messages: 13965 Registered: September 2008 Location: Rainy Manchester
|
Senior Member |
|
|
In addition to what flyboy has said I think you're missing a seperate where clause for the update itself.
The where clause you've got in the subquery solely effects the values you are using to update the table.
Since it appears you don't want to update all rows you need a seperate where clause after the subquery to specify which rows to update.
|
|
|
|
|
Re: regarding a query [message #349022 is a reply to message #349014] |
Thu, 18 September 2008 10:31   |
orasuman7
Messages: 4 Registered: July 2008 Location: BANGALORE
|
Junior Member |
|
|
1 update emp2
2 set sal=
3 (select max(sal) from emp)
4 where abs(to_number(to_char(hiredate,'yyyy')-to_char
5* (sysdate,'yyyy')))>10
SQL> /
14 rows updated.
|
|
|
|
|
|
|
|