SQL problem [message #439567] |
Mon, 18 January 2010 07:42  |
prachij593
Messages: 266 Registered: May 2009
|
Senior Member |
|
|
SQL> select * from test601;
BID PAGE C STATUS
---------- ---------- ---------- ------------------------------
101 1 1
102 1 2
102 2 2
103 1 3
for a book(BID) there are pages(PAGE), c is the total count of pages
if C matches with total count of pages for a book then update the status
select * from test601;
BID PAGE C STATUS
---------- ---------- ---------- ------------------------------
101 1 1 Y
102 1 2 Y
102 2 2 Y
103 1 3 N
How can i get the result?
[Updated on: Mon, 18 January 2010 07:51] Report message to a moderator
|
|
|
|
Re: SQL problem [message #439572 is a reply to message #439567] |
Mon, 18 January 2010 07:54   |
pablolee
Messages: 2882 Registered: May 2007 Location: Scotland
|
Senior Member |
|
|
Use count in its analytic form (you can find out the syntax from the documentation that you hav been pointed to many, many times.
|
|
|
|
Re: SQL problem [message #439580 is a reply to message #439577] |
Mon, 18 January 2010 08:15   |
pablolee
Messages: 2882 Registered: May 2007 Location: Scotland
|
Senior Member |
|
|
Stop trying to do everything at once. bring in all of your columns and add in the analytic into your select list.
Lose the order by, lose the where clause, start using column names as described in the original example. Partition by BID and please learn how to post a test case, and how to use a more appropriate title for your posts.
[Edit: Of course, some of the above advice may seem pointless as it was posted whilst you were editing the first version of the above post]
[Updated on: Mon, 18 January 2010 08:19] Report message to a moderator
|
|
|
Re: SQL problem [message #439581 is a reply to message #439580] |
Mon, 18 January 2010 08:21   |
prachij593
Messages: 266 Registered: May 2009
|
Senior Member |
|
|
SQL> update test601 a set status=
2 (select (case when count(*) over (partition by b.a order by b.a) =c then 'Y' else 'N' end) e
from test601 b
3 where b.a=a.a and b.b=a.b
4 )
5 /
4 rows updated.
SQL> select * from test601;
A B C STATUS
---------- ---------- ---------- ------------------------------
101 1 1 Y
102 1 2 N
102 2 2 N
103 1 3 N
SQL>
But I am not getting the result I expected. What needs to be corrected?
[Updated on: Mon, 18 January 2010 08:36] Report message to a moderator
|
|
|
|
Re: SQL problem [message #439583 is a reply to message #439582] |
Mon, 18 January 2010 08:40   |
prachij593
Messages: 266 Registered: May 2009
|
Senior Member |
|
|
I tried this way:
SQL> select a,c, case when count(*) over (partition by a order by a) =c then 'Y' else 'N' end e from
test601;
A C E
---------- ---------- -
101 1 Y
102 2 Y
102 2 Y
103 3 N
SQL> select * from test601;
A B C STATUS
---------- ---------- ---------- ------------------------------
101 1 1
102 1 2
102 2 2
103 1 3
[Updated on: Mon, 18 January 2010 08:59] Report message to a moderator
|
|
|
Re: SQL problem [message #439584 is a reply to message #439583] |
Mon, 18 January 2010 09:04   |
prachij593
Messages: 266 Registered: May 2009
|
Senior Member |
|
|
update test601 a set a.status=
(select case when count(*) over (partition by b.a order by b.a) =b.c then 'Y' else 'N' end e from
test601 b where b.a=a.a and a.c=b.c);
Not being able to figure out where is the problem!
[Updated on: Mon, 18 January 2010 09:05] Report message to a moderator
|
|
|
Re: SQL problem [message #439585 is a reply to message #439584] |
Mon, 18 January 2010 09:12   |
prachij593
Messages: 266 Registered: May 2009
|
Senior Member |
|
|
How can I solve the problem?
SQL> update test601 a set status= (select case when count(*) over (partition by a order by a) =c th
en 'Y' else 'N' end e from test601 where a.a=a and a.c=c);
update test601 a set status= (select case when count(*) over (partition by a order by a) =c then 'Y
*
ERROR at line 1:
ORA-01427: single-row subquery returns more than one row
SQL>
|
|
|
|
|
Re: SQL problem [message #439590 is a reply to message #439588] |
Mon, 18 January 2010 09:32   |
prachij593
Messages: 266 Registered: May 2009
|
Senior Member |
|
|
update test601 a set status = ( select case when count(*) over (partition by b.a order by b.a)
=b.c then 'N' else 'Y' end e from test601 B where b.a=a.a and b.b=a.b and b.c=a.c)
/
|
|
|
Re: SQL problem [message #439592 is a reply to message #439590] |
Mon, 18 January 2010 09:39   |
prachij593
Messages: 266 Registered: May 2009
|
Senior Member |
|
|
SQL> ed
Wrote file afiedt.buf
1 UPDATE test601 a set a.status=
2 (SELECT STATUS FROM ( select A,B,C ,case when count(*) over (partition by a order by a)
3* =c then 'Y' else 'N' end STATUS from test601 )sq where sq.a=a.a and sq.b=a.b and sq.c=a.c)
SQL> /
4 rows updated.
SQL> select * from test601;
A B C STATUS
---------- ---------- ---------- ------------------------------
101 1 1 Y
102 1 2 Y
102 2 2 Y
103 1 3 N
SQL>
|
|
|
|
|
|
|
|
|
|
|
|
Re: SQL problem [message #440457 is a reply to message #440440] |
Mon, 25 January 2010 00:55  |
Frank
Messages: 7901 Registered: March 2000
|
Senior Member |
|
|
Since your outcome does not resemble the desired outcome in the original post, I don't think that you understood the requirements.
Besides, the original poster already posted back his solution.
|
|
|