OraFAQ Forum: SQL & PL/SQL » update statement

Home » SQL & PL/SQL » SQL & PL/SQL » update statement (Oracle 10.2.0.1, Windows 2003)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

update statement [message #345447]

Wed, 03 September 2008 11:47

sant_new
Messages: 165
Registered: June 2008

Senior Member

Hi all,

I need to update a field with the value from another table.

1st Table NIT has fields, cno and desc

2nd Table CIT has fields, cno and itm

Below are the values in CIT and NIT.

Table NIT

CNO 	DESC

100	temp
100	temp2	
101	2455
101	2466
.
.
.
.

Table CIT

CNO	ITM

100	0163
100	PL01
101	MEE12
101	WEK11
.
.
.

I need to add new field 'desc' to CIT table and populate the CIT.desc from NIT.desc based on the cno.
But the trick is, I need to populate the value of first cno in the CIT.desc. FOr example,

for CIT.cno=100, I need to populate CIT.desc with 'temp' from NIT.desc and not temp2.
for CIT.cno=101, I need to populate CIT.desc with '2455' from NIT.desc and not 2466.

I am not sure how I can do this. I tried below but no success.

update cit c1 set (c1.desc) = (select n1.desc from nit n1 where c1.cno=n1.cno);

Any help will be appreciated.Thank you

Report message to a moderator

Re: update statement [message #345451 is a reply to message #345447]

Wed, 03 September 2008 12:06

Littlefoot
Messages: 21808
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

If the "first" means "minimum" (might be so, according to your example), you could try with something like

UPDATE first_table f SET
  f.some_column = (SELECT MIN(a.some_column)
                   FROM another_table a
                   WHERE a.some_id = f.some_id
                  )
WHERE EXISTS (SELECT NULL 
              FROM another_table a1
              WHERE a1.some_id = f.some_id
             );

Report message to a moderator

Re: update statement [message #345452 is a reply to message #345447]

Wed, 03 September 2008 12:07

Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Define "first".

Post a test case: create table and insert statements along with the result you want with these data.

Regards
Michel

Report message to a moderator

Re: update statement [message #345461 is a reply to message #345447]

Wed, 03 September 2008 12:38

sant_new
Messages: 165
Registered: June 2008

Senior Member

Okay. I meant the CIT.descr should be populated with NIT.descr from the first inserted record in NIT.

Below is the test case:

SQL> select * from nit;

CNO        DESCR
---------- ------------------------------
100        temp
100        temp2
101        2455
101        2466
101        2242
101        edil

6 rows selected.

SQL> select * from cit;

CNO        ITM
---------- ------------------------------
100        0163
100        PL01
101        MEE12
101        WEK11

After I add descr field to CIT, the result of the update to CIT.descr should be like:

select * from cit;

CNO        ITM		DESCR
---------- ------	
100        0163		temp
100        PL01		temp
101        MEE12	2455
101        WEK11	2455

Thank you for all the help.

Report message to a moderator

Re: update statement [message #345462 is a reply to message #345447]

Wed, 03 September 2008 12:46

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

Rows in a table are like balls in a basket.
How do you identify the "first" red ball in the basket?

In any RDBMS table there is no inherent "first" row!

Report message to a moderator

Re: update statement [message #345463 is a reply to message #345447]

Wed, 03 September 2008 12:58

sant_new
Messages: 165
Registered: June 2008

Senior Member

Anacedent,

I agree with you. But my boss wants me to find out if there is any possible way to do this.

When I do a select * from nit, the order of the rows is the same all the time. What is needed is, the value of the first occuring row from the select statement. Is is possible to do a
select n1.descr from nit n1,cit c1 where c1.cno=n1.cno. And get the n1.descr from the first row and update in CIT.descr??

If this is impossible, I have to find alternate possibilities.

Thanks again

Report message to a moderator

Re: update statement [message #345464 is a reply to message #345463]

Wed, 03 September 2008 13:17

Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Quote:

select * from nit, the order of the rows is the same all the time.

This is wrong and I posted several examples, here for instance.

Quote:

If this is impossible, I have to find alternate possibilities.

You just have to define "first"; maybe it is "any" and so not deterministic.

Still waiting for create table and insert statements.

Regards
Michel

[Updated on: Wed, 03 September 2008 13:18]

Report message to a moderator

Re: update statement [message #345466 is a reply to message #345447]

Wed, 03 September 2008 13:23

sant_new
Messages: 165
Registered: June 2008

Senior Member

Michael,

I've posted the test case already.. Please take a look..

Is it possible to get the value from the select statement returning multiple rows??

For example, I need to get the value of cit.descr from the below select statement (value of CIT.descr= value of the n1.descr from the 1st returned row of matching c1.cno=n1.cno)

update cit c1 set (c1.descr) = (select n1.descr from nit n1 where c1.cno=n1.cno);

Thank you

Report message to a moderator

Re: update statement [message #345469 is a reply to message #345466]

Wed, 03 September 2008 13:39

Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Quote:

I've posted the test case already.. Please take a look...

Wrong: create table and insert statements.
Why should I write them to help you. You have to help us help you if you want help.

Regards
Michel

Report message to a moderator

Re: update statement [message #345471 is a reply to message #345447]

Wed, 03 September 2008 13:50

sant_new
Messages: 165
Registered: June 2008

Senior Member

Okay, Sorry about that. I thought thats what you needed.

Below are the create tables and insert statements:

CREATE TABLE nit
(
  cno  VARCHAR2(10)  DEFAULT ' ',
   descr  VARCHAR2(30) DEFAULT ' '
   );
   
   
CREATE TABLE cit
(
  cno  VARCHAR2(10)  DEFAULT ' ',
  itm  VARCHAR2(30) DEFAULT ' '
 );

insert into nit values ('100','temp');
insert into nit values ('100','temp2');
insert into nit values ('101','2455');
insert into nit values ('101','2466');
insert into nit values ('101','2242');
insert into nit values ('101','edi1');
insert into cit values ('100','0163');
insert into cit values ('100','PL01');
insert into cit values ('101','MEE12');
insert into cit values ('101','WEK11');

alter table cit add (descr varchar2(30) default ' ');


SQL> select * from nit;

CNO        DESCR
---------- ------------------------------
100        temp
100        temp2
101        2455
101        2466
101        2242
101        edi1

6 rows selected.

SQL> select * from cit;

CNO        ITM        DESCR
---------- --------- ---------
100        0163
100        PL01
101        MEE12
101        WEK11

After the update of cit.descr, the result should be like:

SQL> select * from cit;

CNO        ITM        DESCR
---------- --------- ---------
100        0163       temp
100        PL01       temp
101        MEE12      2455
101        WEK11      2455

I need to get the value of cit.descr from the select statement (value of CIT.descr= value of the n1.descr from the 1st returned row of matching c1.cno=n1.cno)

update cit c1 set (c1.descr) = (select n1.descr from nit n1 where c1.cno=n1.cno);

Thanks for any help.

Report message to a moderator

Re: update statement [message #345472 is a reply to message #345447]

Wed, 03 September 2008 13:52

sant_new
Messages: 165
Registered: June 2008

Senior Member

Also, this will be a one time update statement and the table is considerably small. So I am hoping this can be done with an update statement. Thanks a lot.

Report message to a moderator

Re: update statement [message #345473 is a reply to message #345447]

Wed, 03 September 2008 14:06

sant_new
Messages: 165
Registered: June 2008

Senior Member

I am working with sqlserver as well for the exact same update statement, the below update works:

Update c1 set descr = n1.descr 
 From cit c1
  Join nit n1 ON c1.cno=n1.cno

In oracle, I get error:

ORA-00933: SQL command not properly ended

Thanks.

Report message to a moderator

Re: update statement [message #345474 is a reply to message #345472]

Wed, 03 September 2008 14:09

Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

But there is no answer until you define "first".
No one can give you "first" if you don't say how we can know it is first, second, third...

Regards
Michel

[Updated on: Wed, 03 September 2008 14:09]

Report message to a moderator

Re: update statement [message #345478 is a reply to message #345471]

Wed, 03 September 2008 14:53

joicejohn
Messages: 327
Registered: March 2008
Location: India

Senior Member

Hi,

I was under the impression that you can solve this kind of problem either by using ROWID or ROWNUM Pseudocolumn. However the following
link cleared my doubts. You can't define a First Record or Last Record in a table unless you have a timestamp column associated with that table.

Please go through the following link:

http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:912210644860

Regards,
Jo

Report message to a moderator

Re: update statement [message #345484 is a reply to message #345463]

Wed, 03 September 2008 15:28

joy_division
Messages: 4963
Registered: February 2005
Location: East Coast USA

Senior Member

sant_new wrote on Wed, 03 September 2008 13:58

When I do a select * from nit, the order of the rows is the same all the time.

Here is one of my favorite examples, from both anacedent and Frank. I liked the example so much, I bought the company.

FOO SCOTT>set echo on feedback on
FOO SCOTT>create table test1 (a number, b varchar2(10));

Table created.

FOO SCOTT>insert into test1 values (1,'Red');

1 row created.

FOO SCOTT>insert into test1 values (2,'Green');

1 row created.

FOO SCOTT>insert into test1 values (3,'Yellow');

1 row created.

FOO SCOTT>select * from test1 order by a;

         A B
---------- ----------
         1 Red
         2 Green
         3 Yellow

3 rows selected.

FOO SCOTT>delete from test1 where a = 2;

1 row deleted.

FOO SCOTT>select * from test1 order by a;

         A B
---------- ----------
         1 Red
         3 Yellow

2 rows selected.

FOO SCOTT>insert into test1 values (2,'Red');

1 row created.

FOO SCOTT>select * from test1 order by a;

         A B
---------- ----------
         1 Red
         2 Red
         3 Yellow

3 rows selected.

FOO SCOTT>insert into test1 values (2,'Green');

1 row created.

FOO SCOTT>select * from test1 order by a;

         A B
---------- ----------
         1 Red
         2 Green
         2 Red
         3 Yellow

4 rows selected.

FOO SCOTT>

Report message to a moderator

Re: update statement [message #345506 is a reply to message #345484]

Wed, 03 September 2008 21:57

harrysmall3
Messages: 109
Registered: April 2008
Location: Massachusetts

Senior Member

That is a good example. I'm going to take the leap of faith,
sant_new, that the table data right now is static - ie no update/insert/deletes will be occuring against it before you do this?

If you do a


SELECT DESC FROM NIT WHERE CNO = 100 AND ROWNUM < 2;

Does that return what you would want for id 100? I have no idea if that will return 'temp' but if it does, (and does for all other CNO's) then I suppose you could use a nested select.

No guarantee whatsoever but see what this returns:

SELECT N2.CNO,
  (SELECT N1.DESC FROM NIT N1 WHERE N1.CNO = N2.CNO 
   AND ROWNUM < 2) MaybeFirstDesc
FROM NIT N2;

I'm not at a terminal so I can't test this out. If it works then I'll leave you to making the Update statement. I'd just create a 3rd table (CREATE TABLE CIT_TEMP as SELECT) with the join and replace CIT with it.

The old school method if I were to treat this as a simple exercise in control break logic would be to create a cursor
for NIT and update the desc of CIT with the description from the first record after the control break (Where next CNO not equal to previous CNO).

Quote:

psuedologic -
have a cursor of all recs from NIT, loop until End of Cursor:

FIRST fetch or lastCNO <> currentCNO?
IF YES then
update CIT desc for this CNO, set var lastCNO = currentCNO
ELSE Fetch another NIT record

Again I have no idea if the values returned will be the "first" that you want, but it will be the 'literal' first one returned processed wise.

Regards,
Harry

Report message to a moderator

Re: update statement [message #345535 is a reply to message #345506]

Thu, 04 September 2008 00:56

Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

"SELECT DESC FROM NIT WHERE CNO = 100 AND ROWNUM < 2;" may (or not) return a different result each time you execute it, even if the table is static.

Regards
Michel

Report message to a moderator

Re: update statement [message #345718 is a reply to message #345447]

Thu, 04 September 2008 10:40

sant_new
Messages: 165
Registered: June 2008

Senior Member

The below command works.

update cit c1 set descr = (select n1.descr from nit n1 where n1.cno=c1.cno and rownum<2)

Thanks all, for the help. I appreciate it.

Report message to a moderator

Previous Topic:	schema
Next Topic:	Update in two table by one update statement

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Apr 26 10:39:14 CDT 2024