Home » SQL & PL/SQL » SQL & PL/SQL » update query problem (oracle,9.2.0.8,unix)
update query problem [message #283181] Mon, 26 November 2007 05:44 Go to next message
vickey_sagar1
Messages: 77
Registered: August 2007
Location: pune
Member

Hi,

I am trying to update COMMENT_NUM field in PRRQ_COMMENT table.

sample data like

COMMENT_id     review_id   comment_type   COMMENT_NUM 
10                  50        G              31
20                  50        G              15 
30                  50        I               4
40                  50        I               5

expected output

COMMENT_id     review_id  comment_type    COMMENT_NUM 
10                  50         G              1
20                  50         G              2
30                  50         I              1
40                  50         I              2


query

update PRRQ_COMMENT t
set (COMMENT_NUM)=
(SELECT RANK() OVER (PARTITION BY t1.REVIEW_ID,t1.comment_type ORDER BY t1.COMMENT_id) rank1
FROM   PRRQ_COMMENT t1 WHERE 
t1.COMMENT_id=t.COMMENT_id
and 
t1.comment_type='G' AND t1.review_id=50
)
where t.comment_type='G' AND t.review_id=50

output comg

COMMENT_id     review_id  comment_type    COMMENT_NUM 
10                  50         G            1
20                  50         G            1 
30                  50         I            1
40                  50         I            1


Please let me know how I will achieve my requirement.

Thanks,
Sagar
Re: update query problem [message #283183 is a reply to message #283181] Mon, 26 November 2007 05:49 Go to previous messageGo to next message
Michel Cadot
Messages: 64106
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You have to nested once more the subquery with analytic function and also return the rowid.
Then join with updated table by this rowid.

If you'd posted a test case (create table and insert statements) I'd posted the query.

Regards
Michel
Re: update query problem [message #283193 is a reply to message #283183] Mon, 26 November 2007 06:07 Go to previous messageGo to next message
vickey_sagar1
Messages: 77
Registered: August 2007
Location: pune
Member

Thx Michle. i am not able to get the require result. Please find the table structure and data


create table PRRQ_COMMENT (COMMENT_id number,review_id number,comment_type varchar2(1),COMMENT_NUM number);

insert into  PRRQ_COMMENT values (10,50,'G',31);

insert into  PRRQ_COMMENT values (20,50,'G',15);

insert into  PRRQ_COMMENT values (30,50,'I',4);

insert into  PRRQ_COMMENT values (40,50,'I',5);



Thanks,
Sagar
Re: update query problem [message #283215 is a reply to message #283181] Mon, 26 November 2007 06:38 Go to previous messageGo to next message
kir_ait
Messages: 198
Registered: November 2007
Location: Bangalore,India
Senior Member

Hey Sagar,

check this out, it may helpful for you:

DECLARE
BEGIN
   FOR i IN (SELECT   comment_type
                 FROM prrq_comment
             ORDER BY comment_type ASC)
   LOOP
      FOR j IN
         (SELECT RANK () OVER (PARTITION BY t1.review_id, t1.comment_type ORDER BY t1.comment_id)
                                                                       rank1,
                 t1.comment_id comm_id
            FROM prrq_comment t1
           WHERE t1.comment_type = i.comment_type AND t1.review_id = 50)
      LOOP
         UPDATE prrq_comment
            SET comment_num = j.rank1
          WHERE comment_id = j.comm_id AND comment_type = i.comment_type;

         COMMIT;
      END LOOP;
   END LOOP;
END;


Regards,
Kiran
Re: update query problem [message #283216 is a reply to message #283193] Mon, 26 November 2007 06:39 Go to previous messageGo to next message
Michel Cadot
Messages: 64106
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SQL> select comment_id, review_id, comment_type,
  2         rank() over (partition by review_id, comment_type order by comment_id) rk
  3  from PRRQ_COMMENT 
  4  /
COMMENT_ID  REVIEW_ID C         RK
---------- ---------- - ----------
        10         50 G          1
        20         50 G          2
        30         50 I          1
        40         50 I          2

4 rows selected.

SQL> update PRRQ_COMMENT a
  2  set COMMENT_NUM = (select rk 
  3                     from (select rowid rid,
  4                                  rank() over 
  5                                    (partition by review_id, comment_type 
  6                                     order by comment_id) rk
  7                           from PRRQ_COMMENT ) b
  8                     where a.rowid = b.rid)
  9  /

4 rows updated.

SQL> select * from PRRQ_COMMENT order by comment_id;
COMMENT_ID  REVIEW_ID C COMMENT_NUM
---------- ---------- - -----------
        10         50 G           1
        20         50 G           2
        30         50 I           1
        40         50 I           2

4 rows selected.

You can also use MERGE instead (easier to read):
SQL> rollback;

Rollback complete.

SQL> merge into PRRQ_COMMENT a
  2  using ( select rowid rid,
  3                 rank() over (partition by review_id, comment_type 
  4                              order by comment_id) rk
  5          from PRRQ_COMMENT ) b 
  6  on ( a.rowid = b.rid )
  7  when matched then update set COMMENT_NUM = b.rk
  8  /

4 rows merged.

SQL> select * from PRRQ_COMMENT order by comment_id;
COMMENT_ID  REVIEW_ID C COMMENT_NUM
---------- ---------- - -----------
        10         50 G           1
        20         50 G           2
        30         50 I           1
        40         50 I           2

4 rows selected.

Regards
Michel
Re: update query problem [message #283218 is a reply to message #283215] Mon, 26 November 2007 06:41 Go to previous messageGo to next message
Michel Cadot
Messages: 64106
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
NEVER use PL/SQL when you can do it in SQL.
Above all this double old fashion loops one.
This is (one of) the worst solution you can find.

Regards
Michel
Re: update query problem [message #283224 is a reply to message #283181] Mon, 26 November 2007 06:51 Go to previous messageGo to next message
kir_ait
Messages: 198
Registered: November 2007
Location: Bangalore,India
Senior Member

Thats much better Smile

Kiran.
Re: update query problem [message #283281 is a reply to message #283224] Mon, 26 November 2007 10:34 Go to previous messageGo to next message
vickey_sagar1
Messages: 77
Registered: August 2007
Location: pune
Member

Thx Michel. it is working fine ..But this query is updating all the records but we need to update the records based on few review ids instead of complete table update. We tried some thing like following but it is not working. Could you please suggest me what change require for that

update PRRQ_COMMENT a
  set COMMENT_NUM = (select rk 
                     from (select rowid rid,comment_id,review_id,
                                  rank() over 
                                    (partition by review_id, comment_type 
                                     order by comment_id) rk
                           from PRRQ_COMMENT where review_id=50 ) b
                     where a.rowid  = b.rid and a.review_id=50)

Re: update query problem [message #283285 is a reply to message #283281] Mon, 26 November 2007 10:39 Go to previous messageGo to next message
Michel Cadot
Messages: 64106
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You have to put a where clause in your update/merge as you did it in your first post.

Regards
Michel
Re: update query problem [message #283301 is a reply to message #283285] Mon, 26 November 2007 11:00 Go to previous messageGo to next message
vickey_sagar1
Messages: 77
Registered: August 2007
Location: pune
Member

thx michle ..really you are great in oracle.
Re: update query problem [message #283342 is a reply to message #283301] Mon, 26 November 2007 15:54 Go to previous message
Littlefoot
Messages: 20892
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
In ancient ages, it was Alexander the Great. History will once teach that in modern age, it was Michel the Great, conquerer of SQL and PL/SQL continets.
Previous Topic: how to find out client version from SQL command.
Next Topic: Combine table info
Goto Forum:
  


Current Time: Mon Dec 05 02:56:16 CST 2016

Total time taken to generate the page: 0.10718 seconds