Home » SQL & PL/SQL » SQL & PL/SQL » regarding a query (sql*plus, winxp)
regarding a query [message #348994] Thu, 18 September 2008 09:08 Go to next message
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 #348996 is a reply to message #348994] Thu, 18 September 2008 09:14 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter) and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version (4 decimals).

Post a test case: create table and insert statements along with the result you want with these data.

Regards
Michel
Re: regarding a query [message #349000 is a reply to message #348994] Thu, 18 September 2008 09:32 Go to previous messageGo to next message
ukdas
Messages: 32
Registered: September 2008
Location: London
Member

hi guys,

i want those agents to be updated (with the earliest date) whose ages are more than 50 and are already been deactivated which they have their deactivation date...

i tried with the following query writing in line 13 is:

deactivation_date IS NOT NULL

but the query populate all the rows with earliest date. don't know why.

Regards
ukd
Re: regarding a query [message #349009 is a reply to message #348994] Thu, 18 September 2008 09:46 Go to previous messageGo to next message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
Your problem is that the subquery is not correlated to the main query; so it returns the same row for all updated rows.

Have a look here: http://www.orafaq.com/wiki/Sub-query (especially the second example). Use table aliases to distinguish which AGENTS table you use.
Re: regarding a query [message #349014 is a reply to message #348994] Thu, 18 September 2008 10:02 Go to previous messageGo to next message
cookiemonster
Messages: 12422
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 #349020 is a reply to message #348994] Thu, 18 September 2008 10:24 Go to previous messageGo to next message
ukdas
Messages: 32
Registered: September 2008
Location: London
Member

THANKS TO flyboy and cookiemaster

yes. now i got the concept right. i put the where clause outside the subquery and it seems that the query is working..

the syntax i tried is like this:

update
agents a
set
a.deactivation_date=
(
select
min(aa.deactivation_date)
from
agents aa
)
WHERE
ABS(TO_NUMBER(TO_CHAR(a.birth_date,'YYYY')-TO_CHAR(SYSDATE, 'YYYY')))>50
AND
a.deactivation_date IS NOT NULL
;
Re: regarding a query [message #349021 is a reply to message #348994] Thu, 18 September 2008 10:25 Go to previous messageGo to next message
ukdas
Messages: 32
Registered: September 2008
Location: London
Member

my apology to cookiemonster as i mentioned cookiemaster.

Regards
ukd
Re: regarding a query [message #349022 is a reply to message #349014] Thu, 18 September 2008 10:31 Go to previous messageGo to next message
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.
Re: regarding a query [message #349024 is a reply to message #349022] Thu, 18 September 2008 10:37 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
@orasuman7 and ukdas

please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter).
Use the "Preview Message" button to verify.

Regards
Michel
Re: regarding a query [message #349069 is a reply to message #348994] Thu, 18 September 2008 15:27 Go to previous messageGo to next message
ukdas
Messages: 32
Registered: September 2008
Location: London
Member

i am trying to go for a bit complex sql query where i need to update those deactivated agents which are involved in more than 7 missions and deactivation_date should be changed to the earliest deactivation_date of all agents that are activated in the same date.

i tried with following one but cann't think any further. i required help solving this :

1 update
2 agents a
3 set
4 a.deactivation_date=
5 (
6 select
7 min(aa.deactivation_date)
8 from
9 agents aa
10 where
11 a.activation_date=aa.activation_date
12 )
13 inner join
14 missions_agents ma ON
15 ma.agent_id=a.agent_id
16 inner join
17 missions m ON
18 m.mission_id=ma.mission_id
19 WHERE
20 m.mission_id>7
21 ;

thanks

Regards
ukd
Re: regarding a query [message #349071 is a reply to message #348994] Thu, 18 September 2008 15:32 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
http://www.orafaq.com/forum/t/88153/0/
Please read & follow posting guidelines as stated in URL above
Re: regarding a query [message #349119 is a reply to message #349069] Thu, 18 September 2008 23:49 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Michel Cadot wrote on Thu, 18 September 2008 17:37
@(orasuman7 and) ukdas

please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter).
Use the "Preview Message" button to verify.

Regards
Michel

Re: regarding a query [message #349122 is a reply to message #348994] Fri, 19 September 2008 00:02 Go to previous message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member

Indent the code using the tags for getting quick and better answers .

I think you are trying for correlated query . Use it properly .

The following links may be helpfull

http://www.techonthenet.com/sql/update.php

http://www.orafaq.com/forum/?t=msg&th=58544/0/

Thumbs Up
Rajuvan.
Previous Topic: Question on ORDER BY
Next Topic: Escaping single inverted comma (') in oracle ststement
Goto Forum:
  


Current Time: Fri Dec 09 13:23:35 CST 2016

Total time taken to generate the page: 0.11740 seconds