Home » SQL & PL/SQL » SQL & PL/SQL » Update Query Needed
Update Query Needed [message #590055] Sat, 13 July 2013 15:28 Go to next message
rasi_85
Messages: 9
Registered: May 2008
Junior Member
Hi,

DB Version : 10g

Please see the below table structure i need update query:

create table test_group_member ( mwtmg_team_group_id number,mwrol_member_role_id number,primary_in varchar2(1),giclg_clg_id number)

insert into test_group_member values (100,1,'Y',2000);
insert into test_group_member values(100,2,'N',2001);

insert into test_group_member values(101,4,'N',2020);
insert into test_group_member values(101,3,'N',2300);
insert into test_group_member values(101,15,'N',2020);
insert into test_group_member values(101,1115,'N',22020);

insert into test_group_member values(104,4,'N',2020);
insert into test_group_member values(104,3,'N',2300);

insert into test_group_member values(105,3,'N',2020);
insert into test_group_member values(105,4,'N',2020);
insert into test_group_member values(105,15,'N',2030);
insert into test_group_member values(105,16,'N',2030);

insert into test_group_member values(106,4,'',2020);
insert into test_group_member values(106,3,'',2020);
insert into test_group_member values(106,15,'',2020);
insert into test_group_member values(106,16,'',2020);

insert into test_group_member values(102,199,'Y',2000);
insert into test_group_member values(102,01,'Y',22200);
insert into test_group_member values(102,81,'Y',244000);
insert into test_group_member values(102,21,'Y',22000);

Select Query:
=============

select mwtmg_team_group_id,mwrol_member_role_id,primary_in,giclg_clg_id
from test_group_member
where mwrol_member_role_id IN (3,4,15,16) and
mwtmg_team_group_id not in (select mwtmg_team_group_id
from test_group_member t1
where exists (select 1
from test_group_member t2
where t1.mwtmg_team_group_id = t2.mwtmg_team_group_id and t1.primary_in='Y' )
) order by mwtmg_team_group_id;


Based on above query i got output below :

mwtmg_team_group_id mwrol_member_role_id primary_in giclg_clg_id
-----------------------------------------------------------------
101 3 N 2300
101 15 N 2020
101 4 N 2020
104 3 N 2300
104 4 N 2020
105 16 N 2030
105 3 N 2020
105 4 N 2020
105 15 N 2030
106 15 2020
106 3 2020
106 4 2020
106 16 2020


I need update query and i need to update primary_in ='Y' for any one record based on mwtmg_team_group_id Please see teh sample required output:

101 3 **Y** 2300
101 15 N 2020
101 4 N 2020
104 3 **Y** 2300
104 4 N 2020
105 16 **Y** 2030
105 3 N 2020
105 4 N 2020
105 15 N 2030
106 15 **Y** 2020
106 3 2020
106 4 2020
106 16 2020


Thanks,
Rasi
Re: Update Query Needed [message #590057 is a reply to message #590055] Sat, 13 July 2013 17:05 Go to previous messageGo to next message
BlackSwan
Messages: 22500
Registered: January 2009
Senior Member
Please read and follow the forum guidelines, to enable us to help you:

http://www.orafaq.com/forum/t/88153/0/


I don't understand why some records are changed, but other records remain unchanged.
Re: Update Query Needed [message #590066 is a reply to message #590055] Sun, 14 July 2013 01:37 Go to previous message
Michel Cadot
Messages: 58568
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
I need update query and i need to update primary_in ='Y' for any one record based on mwtmg_team_group_id


And the "based on" mean?
What is the EXACT rule?

From your previous topic:

Michel Cadot wrote on Sat, 13 July 2013 20:24
From your previous topic:

BlackSwan wrote on Mon, 08 July 2013 20:09
Please read and follow the forum guidelines, to enable us to help you:

http://www.orafaq.com/forum/t/88153/0/


Michel Cadot wrote on Mon, 08 July 2013 20:58
Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code, use code tags and align the columns in result.
Use the "Preview Message" or "Preview Quick Reply" button to verify.
Also always post your Oracle version, with 4 decimals.

Use SQL*Plus and copy and paste your session, the WHOLE session including the error, procedure creation and execution.

Regards
Michel


And I add: With any SQL or PL/SQL question, please, Post a working Test case: create table (including all constraints) and insert statements along with the result you want with these data then we will be able work with your table and data. Explain with words and sentences the rules that lead to this result.

And don't forget:

Michel Cadot wrote on Tue, 26 February 2013 16:55
...
Tell me and I'll forget; show me and I may remember; involve me and I'll understand.

Regards
Michel


And If you don't know how to format a query, learn it using SQL Formatter.


Regards
Michel
Previous Topic: Select updated rows from one table
Next Topic: Analytic function
Goto Forum:
  


Current Time: Fri Jul 25 13:53:09 CDT 2014

Total time taken to generate the page: 0.11464 seconds