| SQL update [message #645311] |
Wed, 02 December 2015 16:48  |
sant_new
Messages: 165
Registered: June 2008
|
Senior Member |
|
|
Hi friends,
I'm trying to update/insert table test4 based on the criteria below. Below are few records from the table but the script is to update a large volume of data..
SQL> select * from test4;
UID_NO TYPE RULE_DUE
-------------------- --------------- ----------
2000 RAF 90
3140 TH 100
6712 JUI 21
233 H6 12
SQL> select * from test1;
SID_NO SID_NO_NEW UNIT_NO
-------------------- -------------------- --------------------
2000 2000_1 unit1
2000 2000_2 unit3
2000 2000_3 unit4
2002 2002 unit16
4500 4500 unit22
For the records with consecutive(duplicates) sid_no in test1, insert(multiply) records in uid_no.test4 with test1.sid_no_new as 2000_1,2000_2,2000_3 etc where test1.sid_no = test4.uid_no. The script should insert records in test4 only when there are consecutive sid_no in test1.
I tried the script below:
insert into test4 t4 (uid_no,type,rule_due)
select t1.sid_no_new,t4.type,t4.rule_due from test4 t4, test1 t1
where t4.uid_no = t1.sid_no and t1.sid_no_new like '%_%'
The above insert script inserts 3 records in test4 but it also includes the original record with uid_no='2000'...
SQL> select * from test4 order by uid_no;
UID_NO TYPE RULE_DUE
-------------------- --------------- ----------
2000 RAF 90
2000_1 RAF 90
2000_2 RAF 90
2000_3 RAF 90
233 H6 12
3140 TH 100
6712 JUI 21
But after the insert/update into the table, result of Test4 should look like:
SQL> select * from test4 order by uid_no;
UID_NO TYPE RULE_DUE
-------------------- --------------- ----------
2000_1 RAF 90
2000_2 RAF 90
2000_3 RAF 90
233 H6 12
3140 TH 100
6712 JUI 21
Please give your suggestions.. THank you so much
|
|
|
|
|
|
| Re: SQL update [message #645314 is a reply to message #645311] |
Wed, 02 December 2015 23:46  |
 |
Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...
|
Senior Member
Account Moderator |
|
|
As you know it:
With any SQL or PL/SQL question, please, Post a working Test case: create table (including all constraints) and insert statements along with the result you want with these data then we will be able work with your table and data. Explain with words and sentences the rules that lead to this result.
If you post a working Test case we can work with your table and data and find a statement.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
