Home » SQL & PL/SQL » SQL & PL/SQL » A small clarification regd update statement.
A small clarification regd update statement. [message #205908] Tue, 28 November 2006 02:19 Go to next message
Harithareddy11
Messages: 9
Registered: November 2006
Location: BANGALORE
Junior Member
hi...


I have 3 tables

1.master
2.tran
3.temp table


I have a column called 'status' in both temp table and tran table

Now i want to update status of temp table with status of tran table by using a primary key which is in master and tran also..

For this i have to write a procedure or just a sql*plus query is
enough?

Let me know..

Regards

Haritha



Re: A small clarification regd update statement. [message #205916 is a reply to message #205908] Tue, 28 November 2006 02:30 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
You can do it with just an update statement.
Re: A small clarification regd update statement. [message #205958 is a reply to message #205916] Tue, 28 November 2006 04:00 Go to previous messageGo to next message
Harithareddy11
Messages: 9
Registered: November 2006
Location: BANGALORE
Junior Member

hi...

I tried..but cant get...how it can be possible only by a update statement..
can i know the query for it..


Regards

Haritha


icon2.gif  Re: A small clarification regd update statement. [message #205961 is a reply to message #205958] Tue, 28 November 2006 04:06 Go to previous messageGo to next message
amul
Messages: 252
Registered: April 2001
Location: Chennai
Senior Member
please lets see your homework
Re: A small clarification regd update statement. [message #205966 is a reply to message #205961] Tue, 28 November 2006 04:19 Go to previous messageGo to next message
Harithareddy11
Messages: 9
Registered: November 2006
Location: BANGALORE
Junior Member
Hi,

I tried with a cursor and this is my code...

DECLARE
  CURSOR Upd_Status IS 
    SELECT m.scno  Status
    FROM   tRan t,
           Master  m
    WHERE  m.scNo = t.scNo;
   Curr_Status  tRan.Status%TYPE;
   v_scNo       tRan.scNo%TYPE;
BEGIN
  OPEN Upd_Status;
  
  LOOP
    FETCH Upd_Status INTO Curr_Status;
    
    UPDATE Main_Profile_taj
    SET    Meter_Status = Curr_Status
    WHERE  scNo = v_scNo;
    
    EXIT WHEN Upd_Status%NOTFOUND;
  END LOOP;
  
  dbms_Output.Put_Line('UPDATED');
  
  CLOSE Upd_Status;
END;
/


I got this error...


     FETCH UPD_STATUS INTO CURR_STATUS ;
     *
ERROR at line 8:
ORA-06550: line 8, column 6:
PLS-00394: wrong number of values in the INTO list of a FETCH statement
ORA-06550: line 8, column 6:
PL/SQL: SQL Statement ignored


let me know solution for this...

Regards,

Haritha
Re: A small clarification regd update statement. [message #205969 is a reply to message #205966] Tue, 28 November 2006 04:24 Go to previous messageGo to next message
vamsi kasina
Messages: 2107
Registered: October 2003
Location: Riyadh, Saudi Arabia
Senior Member
Is this correct?
 CURSOR Upd_Status IS
SELECT m.scno Status
FROM tRan t,
Master m
WHERE m.scNo = t.scNo;

Do you want to show Master.scno as Status?
If so, Curr_Status tRan.Status%TYPE;
should be changed to
Curr_Status Master.scno%TYPE;


Cross check the datatypes once again.

By
Vamsi
Re: A small clarification regd update statement. [message #205972 is a reply to message #205969] Tue, 28 November 2006 04:35 Go to previous messageGo to next message
Harithareddy11
Messages: 9
Registered: November 2006
Location: BANGALORE
Junior Member


hi...

i dont think my code is right..
i think iam unsuccessful in making you understand my problem..

i repeat..

master has scno,name
tran has scno,status
temp table has scno,status,name.. as columns

i want to update temp table status with the status of tran table
by joining both master and tran tables..

This is my problem..
i think now its clear..

Regards,

Haritha


Re: A small clarification regd update statement. [message #205978 is a reply to message #205972] Tue, 28 November 2006 04:48 Go to previous messageGo to next message
vamsi kasina
Messages: 2107
Registered: October 2003
Location: Riyadh, Saudi Arabia
Senior Member
You don't need a cursor in that scenario.
But as you have tried that, change your code.

If you read my previous post
Quote:
Do you want to show Master.scno as Status?

The answer is No, as you want to update Main_Profile_taj.Meter_Status with tRan.Status

Also you are selecting
CURSOR Upd_Status IS
SELECT m.scno Status


means you are trying to update Main_Profile_taj.Meter_Status with Master.scno

So, in the cursor what exactly you need to select? I hope you have the answer now.

By
Vamsi
Re: A small clarification regd update statement. [message #205983 is a reply to message #205978] Tue, 28 November 2006 04:57 Go to previous messageGo to next message
Harithareddy11
Messages: 9
Registered: November 2006
Location: BANGALORE
Junior Member


hi...

i want to select only meter_status ..and i want to update it for all scnos which are both in master and tran..

Regards,
Haritha


Re: A small clarification regd update statement. [message #205986 is a reply to message #205972] Tue, 28 November 2006 05:01 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Assuming that the 'table definitions' (I use the term loosely) you quite are correct, and are
Quote:
master has scno,name
tran has scno,status
temp table has scno,status,name.. as columns

then if you represent them as these table:
create table master (scno number ,name varchar2(30));
create table tran   (scno number ,status varchar2(10));
create table temp   (scno number ,status varchar2(10),name varchar2(30));

then this sql will do the job, and there's no need to involve MASTER at all:
UPDATE temp te
set    te.status = (select tr.status
                    from   tran tr
                    where  tr.scno = te.scno);

If, however, you have multiple rows in the table TRAN for a given SCNO, then you will need some way of deciding which TRAN record you want.
Re: A small clarification regd update statement. [message #206011 is a reply to message #205986] Tue, 28 November 2006 06:11 Go to previous message
Harithareddy11
Messages: 9
Registered: November 2006
Location: BANGALORE
Junior Member


hi..

Thank u very much..


Regards,

Haritha
Previous Topic: oracle log in error
Next Topic: executing oracle procedure in visual basic
Goto Forum:
  


Current Time: Wed Dec 07 14:18:01 CST 2016

Total time taken to generate the page: 0.13516 seconds