Home » SQL & PL/SQL » SQL & PL/SQL » Rebuild the table Structure (merged) (Oracle 10g)
Rebuild the table Structure (merged) [message #576667] Thu, 07 February 2013 01:54 Go to next message
sss111ind
Messages: 484
Registered: April 2012
Location: India
Senior Member


Hi All,
--this for txn details
CREATE TABLE txn_det(
txnid NUMBER PRIMARY KEY,
amount NUMBER,
status varchar2(50),
cust_id NUMBER);
----this for customer details
CREATE TABLE cust_det(
cust_id NUMBER PRIMARY KEY,
cust_name VARCHAR2(50),
cust_acc number(15));

--data to insert for customer table
INSERT INTO cust_det VALUES(101,'Miller','12345');

INSERT INTO cust_det VALUES(201,'Scott','45678');
----data to insert for txn table
INSERT INTO txn_det VALUES('tx0045',123.00,'success',101);

INSERT INTO txn_det VALUES('tx0046',4512.50,'success',101);

insert into txn_det values('tx0049',78.12,'success',101);

INSERT INTO txn_det VALUES('tx0055',123.12,'success',201);

Now THE problem IS cust_det TABLE's cust_id coulmn may contain duplicate.So I thought OF adding THE txn_id COLUMN TO THE cust_det
table but I know that encourgaes redundancy.Any suggestiion how to avoid the duplication would be greatly appreciated.

Regards,
Nathan
Re: Rebuild the table Structure [message #576670 is a reply to message #576667] Thu, 07 February 2013 02:09 Go to previous messageGo to next message
Michel Cadot
Messages: 59756
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Any suggestiion how to avoid the duplication would be greatly appreciated.


As there is a primary key on cust_id it cannot contain duplicates.

Regards
Michel

[Updated on: Thu, 07 February 2013 02:10]

Report message to a moderator

Re: Rebuild the table Structure [message #576675 is a reply to message #576670] Thu, 07 February 2013 03:09 Go to previous messageGo to next message
sss111ind
Messages: 484
Registered: April 2012
Location: India
Senior Member

Hi Michel,

For the requirement we can allow duplicate from now onwards by allowing duplicates.But how to keep the relation between those two tables with less redundancy as well.Please suggest.

Regards,
Nathan
Re: Rebuild the table Structure [message #576678 is a reply to message #576675] Thu, 07 February 2013 03:53 Go to previous messageGo to next message
Michel Cadot
Messages: 59756
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Build a new table to normalize the model.
See Normalization for an example.

Regards
Michel
Re: Rebuild the table Structure [message #576680 is a reply to message #576678] Thu, 07 February 2013 04:04 Go to previous messageGo to next message
sss111ind
Messages: 484
Registered: April 2012
Location: India
Senior Member

As there are millions of history data we can not create a new table and store.So we opted for to add a column txn_id for cust_det table as well.Though it is data redudancy we can't do anything now.So after adding the column the structure will be like
CREATE TABLE cust_det(
cust_id NUMBER PRIMARY KEY,
cust_name VARCHAR2(50),
cust_acc number(15),txn_id  number);


If doing so what might me the problem apart from redundancy and lots of changes in procedure and packages.Any others there....

Regards,
Nathan
Re: Rebuild the table Structure [message #576681 is a reply to message #576680] Thu, 07 February 2013 04:09 Go to previous messageGo to next message
Michel Cadot
Messages: 59756
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
As there are millions of history data we can not create a new table and store.


This is NOT a valid excuse to not create a new table and it is FAR FAR better than to risk an inconsistency.
In the end, millions of rows is not so much, it is just the number of rows I used on my laptop to make performances tests..

Regards
Michel
Re: Rebuild the table Structure [message #576683 is a reply to message #576681] Thu, 07 February 2013 04:45 Go to previous messageGo to next message
sss111ind
Messages: 484
Registered: April 2012
Location: India
Senior Member

Ok Michel,

Suppose I have created a new table also .As per the above table structures scenario what structure would be for new table.I guess you'll be saying all the columns of those two tables to put into a new table and create it.Then also the same duplication happens in a new table as well.Because the same duplicate data we are maintaining in a same table. I am not getting what is the inconsistency if we do by adding txn_id into cust_det table.Can you please explain in details.

Regards,
Nathan
ignoring last low [message #576685 is a reply to message #576667] Thu, 07 February 2013 05:13 Go to previous messageGo to next message
sss111ind
Messages: 484
Registered: April 2012
Location: India
Senior Member

Hi All,

I want to ignore the last row while selecting.suppose I queried like

select * from emp where rownum<=13;

How it will return only 13 rows.i.e max(rows)-1 i required.Please help me.

Regards,
Nathan
Re: ignoring last low [message #576686 is a reply to message #576685] Thu, 07 February 2013 05:27 Go to previous messageGo to next message
muralikri
Messages: 638
Registered: August 2011
Location: chennai
Senior Member

You can try like...

select * from emp where rowid not in (select max(rowid) from emp)
Re: Rebuild the table Structure [message #576688 is a reply to message #576683] Thu, 07 February 2013 05:38 Go to previous messageGo to next message
Michel Cadot
Messages: 59756
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Only YOU know the model and data.
If you read the link I gave you, you will know how to do it.

Regards
Michel
Re: ignoring last low [message #576689 is a reply to message #576686] Thu, 07 February 2013 05:40 Go to previous messageGo to next message
Michel Cadot
Messages: 59756
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I assume that OP's example is just a very (and too) simplified one, so I doubt your "solution" will work in the real case.

@sss111ind

What is the real case? I think you don't want to retrieve all rows but one, any one.

Regards
Michel
Re: ignoring last low [message #576692 is a reply to message #576689] Thu, 07 February 2013 05:53 Go to previous messageGo to next message
sss111ind
Messages: 484
Registered: April 2012
Location: India
Senior Member

Hi All,

I want TO ADD ename TO dept TABLE AS per emp TABLE AND THE DATAs what emp is having.So please help me
ALTER TABLE dept DROP PRIMARY KEY;

ALTER TABLE dept ADD ename VARCHAR2(10);

INSERT INTO dept SELECT * FROM dept WHERE deptno=10;--but one row less should insert because already one row is present for dept 10.

UPDATE dept SET ename=  (SELECT ename FROM emp WHERE deptno=10) WHERE deptno=10;


Step by step I am updating for all deptno enames.

Regards,
Nathan

[Updated on: Thu, 07 February 2013 05:54]

Report message to a moderator

Re: ignoring last low [message #576693 is a reply to message #576692] Thu, 07 February 2013 05:55 Go to previous messageGo to next message
cookiemonster
Messages: 11181
Registered: September 2008
Location: Rainy Manchester
Senior Member
Isn't deptno the pk on dept?
Re: ignoring last low [message #576694 is a reply to message #576693] Thu, 07 February 2013 05:56 Go to previous messageGo to next message
sss111ind
Messages: 484
Registered: April 2012
Location: India
Senior Member

Yes but I droped it if you look for my code then, to match my requirement.

Regards,
Nathan
Re: ignoring last low [message #576695 is a reply to message #576692] Thu, 07 February 2013 06:03 Go to previous messageGo to next message
Michel Cadot
Messages: 59756
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I don't understand what you want to do.
Currently:
QL> select * from dept;
   DEPTNO DNAME          LOC
--------- -------------- -------------
       10 ACCOUNTING     NEW YORK
       20 RESEARCH       DALLAS
       30 SALES          CHICAGO
       40 OPERATIONS     BOSTON

What should be the result?

UPDATE dept SET ename=  (SELECT ename FROM emp WHERE deptno=10) WHERE deptno=10;

SQL> SELECT ename FROM emp WHERE deptno=10;
ENAME
----------
CLARK
KING
MILLER


There are 3 ename, which one you want? Why? What is the rule?

Regards
Michel
Re: ignoring last low [message #576696 is a reply to message #576694] Thu, 07 February 2013 06:04 Go to previous messageGo to next message
Michel Cadot
Messages: 59756
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
to match my requirement.


What requirement? There is none in your post.

Regards
Michel
Re: ignoring last low [message #576697 is a reply to message #576692] Thu, 07 February 2013 06:04 Go to previous messageGo to next message
cookiemonster
Messages: 11181
Registered: September 2008
Location: Rainy Manchester
Senior Member
sss111ind wrote on Thu, 07 February 2013 11:53
INSERT INTO dept SELECT * FROM dept WHERE deptno=10;--but one row less should insert because already one row is present for dept 10.
[/code]

That makes no sense.
Are you sure the target and source tables should both be dept?
Re: ignoring last low [message #576699 is a reply to message #576697] Thu, 07 February 2013 06:16 Go to previous messageGo to next message
sss111ind
Messages: 484
Registered: April 2012
Location: India
Senior Member

Look My straight requirement is to add ename column into dept table along with data.
Delete is not allowed .But rest is allowed.Later the emp is having 14 rows and all the 14 ename should be added to dept's new enames.
suppose my new dept 10th dept no looks like
 select * from dept where depno=10;
   DEPTNO DNAME          LOC               ENAME
--------- -------------- -----------------------
       10 ACCOUNTING     NEW YORK        CLARK
      10 ACCOUNTING     NEW YORK          KING
       10 ACCOUNTING     NEW YORK      MILLER



Like when I'll select * from dept all 15 rows should come.So insert,update,merge script I required to the process.

Regards,
Nathan

[Updated on: Thu, 07 February 2013 06:19]

Report message to a moderator

Re: ignoring last low [message #576702 is a reply to message #576699] Thu, 07 February 2013 07:23 Go to previous messageGo to next message
Michel Cadot
Messages: 59756
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Answer my question:
Quote:
There are 3 ename, which one you want? Why? What is the rule?


Why CLARK, KING and MILLER and not ALLEN, WARD and JONES?

Regards
Michel
Re: ignoring last low [message #576704 is a reply to message #576702] Thu, 07 February 2013 07:26 Go to previous messageGo to next message
sss111ind
Messages: 484
Registered: April 2012
Location: India
Senior Member

Hi Michel,

Because these employees are belong to deptno10.And they should be updated on deptno10 with same data.

Regards,
Nathan
Re: ignoring last low [message #576708 is a reply to message #576704] Thu, 07 February 2013 07:36 Go to previous messageGo to next message
cookiemonster
Messages: 11181
Registered: September 2008
Location: Rainy Manchester
Senior Member
I assume this:
INSERT INTO dept SELECT * FROM dept WHERE deptno=10;--but one row less should insert because already one row is present for dept 10.

should have been
INSERT INTO dept SELECT * FROM emp WHERE deptno=10;--but one row less should insert because already one row is present for dept 10.

Select from emp not dept.

What keys are on emp?
Re: ignoring last low [message #576711 is a reply to message #576704] Thu, 07 February 2013 07:39 Go to previous messageGo to next message
Michel Cadot
Messages: 59756
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
As there are no real connection between the original table and the final one (not the same column, not the same (number of) rows), create a new table with the new rows, drop the old table and rename the new one.

Regards
Michel
Re: ignoring last low [message #576712 is a reply to message #576711] Thu, 07 February 2013 07:41 Go to previous messageGo to next message
Michel Cadot
Messages: 59756
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
What is the REAL issue you are trying to solve?
I bet it is closely related to your previous topic
Why don't you want to explain us instead of trying to force us to find a solution for a silly question?

Regards
Michel
Re: ignoring last low [message #576715 is a reply to message #576712] Thu, 07 February 2013 07:52 Go to previous messageGo to next message
sss111ind
Messages: 484
Registered: April 2012
Location: India
Senior Member

Yes Michel,

It's related with previous topic.I want to achieve the same thing in reality also.
I have to add txn_id column to cust_det table.As per the txn_id column values from txn_det table I have to update the same txn_id.
Due to earlier cust_det table is having primary key status so only one row is there in that table,so for that row an update is required.And rest of rows needs to be inserted as per the data present on txn_det table.
So for this activity I need to create dml script.

No,CookieMonster ,Because already one row is present in dept table ,so after adding ename field I want to update the ename to that field.So rest 2 ename found for deptno 10.So 2 inserts should be similar data which dept is having but ename will come from emp table for deptno 10.

Hope I clearly explained what my problem is.I don't want to achieve in single insert or update .I want a series of inserts and updates to do this.

Regards,
Nathan

[Updated on: Thu, 07 February 2013 07:53]

Report message to a moderator

Re: ignoring last low [message #576716 is a reply to message #576715] Thu, 07 February 2013 07:56 Go to previous messageGo to next message
Michel Cadot
Messages: 59756
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
So insert (that is duplicate the rows as much as there are in the other table) and then update, I don't see the problem.
If you don't know how to do it in SQL, do it in PL/SQL.

I merge the 2 topics.

Regards
Michel
Re: ignoring last low [message #576717 is a reply to message #576715] Thu, 07 February 2013 08:04 Go to previous messageGo to next message
cookiemonster
Messages: 11181
Registered: September 2008
Location: Rainy Manchester
Senior Member
sss111ind wrote on Thu, 07 February 2013 13:52
No,CookieMonster ,Because already one row is present in dept table ,so after adding ename field I want to update the ename to that field.So rest 2 ename found for deptno 10.So 2 inserts should be similar data which dept is having but ename will come from emp table for deptno 10.

At the start there is one row in dept with a deptno of 10.
So this:
INSERT INTO dept SELECT * FROM dept WHERE deptno=10;--but one row less should insert because already one row is present for dept 10.

must insert 0 rows. 1 row minus 1 row equals 0 rows.
Your example doesn't show what you think it does.
If you don't care which record from emp you ignore for the purposes of the insert this will do the job:
INSERT INTO dept 
SELECT d.* 
FROM dept d, emp e 
WHERE d.deptno=10 
AND e.deptno = d.deptno
AND e.rowid NOT IN (SELECT max(rowid) FROM emp WHERE deptno = d.deptno);
Re: ignoring last low [message #576718 is a reply to message #576717] Thu, 07 February 2013 08:48 Go to previous messageGo to next message
sss111ind
Messages: 484
Registered: April 2012
Location: India
Senior Member

Thanks CookieMonster,Michel.

The code really helped me.But I don't know why it is not giving proper result in my case.So I have modified like this.
The whole insert and update can be done one single insert but the last ename is not updating.And I dont know also which ename is pending.
So I have to this following way.For deptno 10 is fine if I want to do whole table data then I think I have to add group by in subquery with removing
deptno in where clause.
INSERT INTO dept 
SELECT d.* 
FROM dept d, emp e 
WHERE d.deptno=10 
AND e.deptno = d.deptno
AND ROWNUM<(SELECT COUNT(*)
FROM dept d, emp e 
WHERE d.deptno=10 
AND e.deptno = d.deptno);

UPDATE dept d SET d.ENAME=(select ename from emp e where e.deptno=d.deptno) where d.deptno=10;


Regards,
Nathan
Re: ignoring last low [message #576722 is a reply to message #576718] Thu, 07 February 2013 09:24 Go to previous messageGo to next message
cookiemonster
Messages: 11181
Registered: September 2008
Location: Rainy Manchester
Senior Member
My insert will do every row in the table if you just remove the:
d.deptno=10 

form the where clause. Not sure why you felt the need to change it.
The update isn't going to work though. It'll set every row in dept for a given deptno to the same ename.
Re: ignoring last low [message #576725 is a reply to message #576722] Thu, 07 February 2013 09:27 Go to previous messageGo to next message
BlackSwan
Messages: 23035
Registered: January 2009
Senior Member
>UPDATE dept d SET d.ENAME=(select ename from emp e where e.deptno=d.deptno) where d.deptno=10;

I would expect UPDATE to throw error since "select ename from emp e where e.deptno=d.deptno" returns more than 1 row.
Re: ignoring last low [message #576727 is a reply to message #576725] Thu, 07 February 2013 09:36 Go to previous messageGo to next message
cookiemonster
Messages: 11181
Registered: September 2008
Location: Rainy Manchester
Senior Member
you're right, it'll error.
Re: ignoring last low [message #576827 is a reply to message #576727] Fri, 08 February 2013 04:32 Go to previous message
sss111ind
Messages: 484
Registered: April 2012
Location: India
Senior Member

Thanks to All.

Finally I got it the solution.

UPDATE dept1 d
SET ename=
  (SELECT ename
  FROM emp e
  WHERE d.deptno=e.deptno
  AND e.ROWID  IN
    (SELECT MAX(ROWID) FROM emp GROUP BY deptno
    )
  );
  
  
INSERT INTO dept1
  (dname,loc,deptno,ename
  )
SELECT d.dname,
  d.loc,
  d.deptno,
  e.ename
FROM emp e,
  dept1 d
WHERE d.deptno   =e.deptno
AND e.ROWID NOT IN
  (SELECT MAX(ROWID) FROM emp GROUP BY deptno
  );


Regards,
Nathan
Previous Topic: Make columns out of records.
Next Topic: How to use the column names generated from Dynamic SQL
Goto Forum:
  


Current Time: Mon Nov 24 08:05:42 CST 2014

Total time taken to generate the page: 0.09126 seconds