Home » SQL & PL/SQL » SQL & PL/SQL » how to update a table using data from a different table
how to update a table using data from a different table [message #303572] Fri, 29 February 2008 18:27 Go to next message
jhodges
Messages: 6
Registered: February 2008
Junior Member
Can someone help me with this sql this is my first plsql code
and I don't know what I'm doing obviously. Thanks for your help.

Table c_pubbed
 C_ACCOUNT_ID                  NUMBER(9)
 C_JOB_NBR  (unique)           NUMBER(9)
 C_DATE                        DATE
 C_REV_TYPE                    VARCHAR2(8)

TABLE c_ncr_tmp
C_PARENT_ACCT_ID          NOT NULL NUMBER(13)
C_ACCOUNT_ID (unique)     NOT NULL NUMBER(13)
C_NCR                     VARCHAR2(3)

This is the logic(?) that's probably not right:
I want to read table C_PUBBED
use the values to read another table c_ncr_tmp.
If there is a match on the 2 account_ids, and also the parent_id matches the account_id then skip this record and get the next one. ( I set xaccount_id = 0).

Else if the account_ids matches and the parent-id is not the
same as the account_id I want to update C_PUBBED with c_rev_type. ( I don't know how to include this).


My code looks below:
create or replace procedure c_upd
as

xcount        number;
xrowcount     number;

xaccount_id   number;
xparent       number;
vc_ncr      varchar2(8);


cursor pubcsr is
select  *
from   c_pubbed
where to_date (c_date, 'dd-MON-yy') = '28-FEB-08';
for update of c_rev_type;

BEGIN
xrowcount := 0;
xcount := 0;

for xjob in pubcsr loop

BEGIN
select c_parent_acct_id, c_account_id, c_ncr
into xparent, xaccount_id, vc_ncr
from C_NCR_TMP
where xjob.c_account_id = c_account_id
and   xjob.c_account_id = c_parent_acct_id;
EXCEPTION
   when no_data_found then
   xaccount_id := 0;
END;


if xaccount_id != 0
   then
   update c_pubbed
   set    c_rev_type = vc_ncr
   where current of pubcsr;
end if;

end loop;
commit;
end;

[Updated on: Fri, 29 February 2008 23:57] by Moderator

Report message to a moderator

Re: how to update a table using data from a different table [message #303585 is a reply to message #303572] Fri, 29 February 2008 22:12 Go to previous messageGo to next message
mshrkshl
Messages: 247
Registered: September 2006
Location: New Delhi
Senior Member
syntax problem.

do like it:-

if xaccount_id != 0
then newvalue=put_the_value_or_column_name
end if
update c_pubbed
set c_rev_type = newvalue
where condition


regards,

[Updated on: Fri, 29 February 2008 22:44]

Report message to a moderator

Re: how to update a table using data from a different table [message #303601 is a reply to message #303572] Sat, 01 March 2008 00:33 Go to previous messageGo to next message
Michel Cadot
Messages: 64109
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
As C_DATE is a date, "to_date (c_date, 'dd-MON-yy')" is an ERROR.

You can do it in a single SQL statement (assuming account_id is a PK for c_ncr_tmp).
update c_pubbed
set C_REV_TYPE = 
    (select C_NCR 
     from c_ncr_tmp
     where c_ncr_tmp.C_ACCOUNT_ID = c_pubbed.C_ACCOUNT_ID) 
where trunc(C_DATE) = to_date('28-FEB-08','dd-MON-yy')
  and exists 
  (select null
   from c_ncr_tmp
   where c_ncr_tmp.C_ACCOUNT_ID = c_pubbed.C_ACCOUNT_ID
     and c_ncr_tmp.C_PARENT_ACCT_ID = c_pubbed.C_ACCOUNT_ID)
/


Next time, put your code between [code][/code] tags as I did it for you.

Regards
Michel
Re: how to update a table using data from a different table [message #303609 is a reply to message #303601] Sat, 01 March 2008 01:57 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Michel Cadot wrote on Sat, 01 March 2008 07:33
to_date('28-FEB-08','dd-MON-yy')

Dates consist of four digits for the year. Whether or not it matters in this case is not so important, people should make it a habit to always use YYYY
Re: how to update a table using data from a different table [message #303612 is a reply to message #303609] Sat, 01 March 2008 02:07 Go to previous messageGo to next message
Michel Cadot
Messages: 64109
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You're right lazyly and wrongly copied and pasted OP's code.

Regards
Michel

[Updated on: Sat, 01 March 2008 02:07]

Report message to a moderator

Re: how to update a table using data from a different table [message #303654 is a reply to message #303585] Sat, 01 March 2008 11:54 Go to previous messageGo to next message
jhodges
Messages: 6
Registered: February 2008
Junior Member
I thought the select into vc_ncr would take care of this?

Where I have:

select c_parent_acct_id, c_account_id, c_ncr
into xparent, xaccount_id, vc_ncr
from C_NCR_TMP
where xjob.c_account_id = c_account_id
and xjob.c_account_id = c_parent_acct_id;

Should I change it to
if xaccount_id != 0
then vc_ncr = c_ncr
end if;
update c_pubbed
set c_rev_type = vc_ncr
where condition


Thank you for your help!
Re: how to update a table using data from a different table [message #303655 is a reply to message #303601] Sat, 01 March 2008 11:57 Go to previous messageGo to next message
jhodges
Messages: 6
Registered: February 2008
Junior Member
Thanks for fixing my code -- sorry I didn't read all of the directions before submitting it.

I tried your code but I keep getting a syntax error.
I wasn't sure where to put the ";".
Re: how to update a table using data from a different table [message #303656 is a reply to message #303654] Sat, 01 March 2008 11:57 Go to previous messageGo to next message
Michel Cadot
Messages: 64109
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Don't use PL/SQL when you can do it with a single SQL statement.
Your whole procedure can be replace by my single statement.

Regards
Michel

[Updated on: Sat, 01 March 2008 11:57]

Report message to a moderator

Re: how to update a table using data from a different table [message #303659 is a reply to message #303656] Sat, 01 March 2008 12:24 Go to previous messageGo to next message
jhodges
Messages: 6
Registered: February 2008
Junior Member
Thank you so much for your help!
However, it seems I have 10 rows that I thought would update
but they're not getting update. These rows all have the same account_id. How can I get this to update every row on the c_pubbed table?

Thanks you!
Re: how to update a table using data from a different table [message #303660 is a reply to message #303572] Sat, 01 March 2008 12:31 Go to previous messageGo to next message
BlackSwan
Messages: 25037
Registered: January 2009
Location: SoCal
Senior Member
>but they're not getting update.
1) you do not provide complete & actual code
2) you do not provide actual data
3) you do not show us what is really happening

Why do you NOT follow posting guidelines?

Why do you expect meaningful answer when you keep all details known only to you?

Re: how to update a table using data from a different table [message #303661 is a reply to message #303659] Sat, 01 March 2008 12:43 Go to previous messageGo to next message
Michel Cadot
Messages: 64109
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
it seems I have 10 rows that I thought would update but they're not getting update. These

Do they satisfy the condition?
Don't just reply, yes. Post the rows.
Post a test case: create table and insert statements.
Also post the result you want with these data.

Regards
Michel
Re: how to update a table using data from a different table [message #303738 is a reply to message #303661] Sun, 02 March 2008 09:35 Go to previous messageGo to next message
jhodges
Messages: 6
Registered: February 2008
Junior Member
I hope this post follows the rules. The table isn't created this way so there could be syntax problems in the table created below but this is the data I'm looking at and expecting to update.

create table c_ncr
(c_parent_acct_id     NUMBER(13) ,
c_account_id          NUMBER(13) not null,
c_ncr                VARCHAR2(03));


create table c_pubbed
(C_ACCOUNT_ID             NUMBER(9),
C_JOB_NBR                 NUMBER(9),
c_DATE                    DATE,
C_REV_TYPE                VARCHAR2(8));

insert into c_pubbed
values(
    78291010,  854544615, 29-FEB-08, NEW,
    78291010,  855657201, 29-FEB-08, NEW,
    78291010,  855657301, 29-FEB-08, NEW,
    78291010,  855657401, 29-FEB-08, NEW,
    78291010,  855657501, 29-FEB-08, NEW,
    78291010,  855657601, 29-FEB-08, NEW,
    78291010,  855657701, 29-FEB-08, NEW,
    78291010,  855657801, 29-FEB-08, NEW,
    78291010,  855657901, 29-FEB-08, NEW,
    78291010,  855658001, 29-FEB-08, NEW);


insert into c_ncr
values(99053706,     78291010, EXT,
       99053706,      6894016, EXT)


create or replace procedure c_upd
as

xcount        number;
xrowcount     number;
xheader_date  date;
xaccount_id   number;
xparent       number;
maxheader_seq_nbr  number;
vc_ncr      varchar2(8);

--select max(header_seq_nbr) into maxheader_seq_nbr
--from salesforce_stat;

--select header_date  into xheader_date
--from salesforce_stat
--  where header_seq_nbr  = maxheader_seq_nbr;


cursor pubcsr is
select  *
from   c_pubbed
where to_date (c_date, 'dd-MON-yy') = '29-FEB-08'
for update of c_rev_type;

Begin

xrowcount := 0;
xcount := 0;
--select max(header_seq_nbr) into maxheader_seq_nbr
--from s_stat;

--select header_date  into xheader_date
--from s_stat
--where r_seq_nbr  = maxheader_seq_nbr
--and rownum < 2;

for xjob in pubcsr loop
BEGIN
select c_parent_acct_id, c_account_id, c_ncr
into xparent, xaccount_id, vc_ncr
from c_ncr
where xjob.c_account_id = c_account_id
and   xjob.c_account_id = c_parent_acct_id;
EXCEPTION
   when no_data_found then
   xaccount_id := 0;
END;

if xaccount_id != 0
   then
   update c_pubbed
   set    c_rev_type = vc_ncr
   where current of pubcsr;
end if;
end loop;
commit;
end;



The results are:

SQL> select * from c_pubbed where c_account_id = 78291010;

C_ACCOUNT_ID C_JOB_NBR C_DATE C_REV_TY
------------ ---------- --------- --------
78291010 855657201 29-FEB-08 NEW
78291010 855657301 29-FEB-08 NEW
78291010 855657401 29-FEB-08 NEW
78291010 855657501 29-FEB-08 NEW
78291010 855657601 29-FEB-08 NEW
78291010 855657701 29-FEB-08 NEW
78291010 855657801 29-FEB-08 NEW
78291010 855657901 29-FEB-08 NEW
78291010 855658001 29-FEB-08 NEW
78291010 854544615 29-FEB-08 NEW

10 rows selected.


Thanks for your time.




[Updated on: Sun, 02 March 2008 09:38]

Report message to a moderator

Re: how to update a table using data from a different table [message #303740 is a reply to message #303738] Sun, 02 March 2008 10:40 Go to previous messageGo to next message
Michel Cadot
Messages: 64109
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
1/ Post real statements. Your inserts are not SQL.
2/ What is the difference between input and results? It seems your procedure does nothing which is normal as no value satisfies the condition.
so what do you want to do with that?
Did you try the statement I posted?

Regards
Michel
Re: how to update a table using data from a different table [message #303757 is a reply to message #303740] Sun, 02 March 2008 12:19 Go to previous messageGo to next message
jhodges
Messages: 6
Registered: February 2008
Junior Member
Sorry I can't create the tables the way they're created here.
I thought typing in an insert would be okay. I thought this
forum was for people NEW to pl/sql to get help not berated.

I tried this query and 362 rows were updated but I don't know
how to display those rows. I'm sure you'll make me feel stupid
here too. All I know is the data I expected to update did not
update.

As stated in my original post I admitted my query was probably
not correct and even questioned myself for calling it logic.
I simply expected the c_rev_type on the records listed below
to change from "NEW" to "EXT" on the c_pubbed table because "EXT" is on the table
C_NCR where that account = 78291010, and the parent_acct_id (99053706) is not equal.

Sometimes the c_ncr.parent_acct_id = the c_ncr.account_id and I want to skip updating the c_pubbed.c_rev_type.



1 update c_pubbed
2 set C_REV_TYPE =
3 (select C_NCR
4 from c_ncr
5 where c_ncr.C_ACCOUNT_ID = c_pubbed.C_ACCOUNT_ID)
6 where trunc(C_DATE) = to_date('29-FEB-08','dd-MON-yy')
7 and exists
8 (select null
9 from c_ncr
10 where c_ncr.C_ACCOUNT_ID = c_pubbed.C_ACCOUNT_ID
11* and c_ncr.C_PARENT_ACCT_ID = c_pubbed.C_ACCOUNT_ID)
SQL> /
362 rows updated.

SQL> select * from c_pubbed
2 where c_account_id = 78291010;

C_ACCOUNT_ID C_JOB_NBR C_DATE C_REV_TY
------------ ---------- --------- --------
78291010 855657201 29-FEB-08 NEW
78291010 855657301 29-FEB-08 NEW
78291010 855657401 29-FEB-08 NEW
78291010 855657501 29-FEB-08 NEW
78291010 855657601 29-FEB-08 NEW
78291010 855657701 29-FEB-08 NEW
78291010 855657801 29-FEB-08 NEW
78291010 855657901 29-FEB-08 NEW
78291010 855658001 29-FEB-08 NEW
78291010 854544615 29-FEB-08 NEW

10 rows selected.

SQL> select * from c_ncr
2 where c_account_id = 78291010;

C_PARENT_ACCT_ID C_ACCOUNT_ID C_N
---------------- ------------ ---
99053706 78291010 EXT



Thank you for all of your help.
Re: how to update a table using data from a different table [message #303759 is a reply to message #303757] Sun, 02 March 2008 12:22 Go to previous message
Michel Cadot
Messages: 64109
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
that account = 78291010, and the parent_acct_id (99053706) is not equal.

Ah!
Quote:
10 where c_ncr.C_ACCOUNT_ID = c_pubbed.C_ACCOUNT_ID
11* and c_ncr.C_PARENT_ACCT_ID = c_pubbed.C_ACCOUNT_ID)

So why do you test equal?

Regards
Michel
Previous Topic: One time Only procedures
Next Topic: how make IN operator not case sensitive ?
Goto Forum:
  


Current Time: Mon Dec 05 08:31:19 CST 2016

Total time taken to generate the page: 0.15377 seconds