Home » SQL & PL/SQL » SQL & PL/SQL » update statement (Oracle 10.2.0.1, Windows 2003)
update statement [message #345447] Wed, 03 September 2008 11:47 Go to next message
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
Re: update statement [message #345451 is a reply to message #345447] Wed, 03 September 2008 12:06 Go to previous messageGo to next message
Littlefoot
Messages: 20900
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
             );
Re: update statement [message #345452 is a reply to message #345447] Wed, 03 September 2008 12:07 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
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
Re: update statement [message #345461 is a reply to message #345447] Wed, 03 September 2008 12:38 Go to previous messageGo to next message
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.
Re: update statement [message #345462 is a reply to message #345447] Wed, 03 September 2008 12:46 Go to previous messageGo to next message
BlackSwan
Messages: 25046
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!
Re: update statement [message #345463 is a reply to message #345447] Wed, 03 September 2008 12:58 Go to previous messageGo to next message
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
Re: update statement [message #345464 is a reply to message #345463] Wed, 03 September 2008 13:17 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
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 Go to previous messageGo to next message
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
Re: update statement [message #345469 is a reply to message #345466] Wed, 03 September 2008 13:39 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
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
Re: update statement [message #345471 is a reply to message #345447] Wed, 03 September 2008 13:50 Go to previous messageGo to next message
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.
Re: update statement [message #345472 is a reply to message #345447] Wed, 03 September 2008 13:52 Go to previous messageGo to next message
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.
Re: update statement [message #345473 is a reply to message #345447] Wed, 03 September 2008 14:06 Go to previous messageGo to next message
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.
Re: update statement [message #345474 is a reply to message #345472] Wed, 03 September 2008 14:09 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
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 Go to previous messageGo to next message
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
Re: update statement [message #345484 is a reply to message #345463] Wed, 03 September 2008 15:28 Go to previous messageGo to next message
joy_division
Messages: 4642
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>
Re: update statement [message #345506 is a reply to message #345484] Wed, 03 September 2008 21:57 Go to previous messageGo to next message
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
Re: update statement [message #345535 is a reply to message #345506] Thu, 04 September 2008 00:56 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
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
Re: update statement [message #345718 is a reply to message #345447] Thu, 04 September 2008 10:40 Go to previous message
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.
Previous Topic: schema
Next Topic: Update in two table by one update statement
Goto Forum:
  


Current Time: Thu Dec 08 08:13:33 CST 2016

Total time taken to generate the page: 0.10974 seconds