|
View previous topic :: View next topic
|
| Author |
Message |
Bhagyalakshmi C
New User
Joined: 24 Jul 2007
Posts: 38
Location: Bangalore
|
|
|
|
Hi,
I have a table like this..
eno----ename----addr
111----aaaaa-----north
222----bbbbb-----east
333----cccccc-----west
333----cccccc-----west
333----cccccc-----west
333----cccccc-----west
this is a simple table. No primary key and no unique constraint.
i have executed the last insert statement 4 times and the same record got inserted into the table 4 times.
i need to delete the three records and keep only one copy..
Can any one please help me!!!
Hope u understood what i want!!
Thank you!!! |
|
| Back to top |
|
 |
murmohk1
Senior Member
.jpg)
Joined: 29 Jun 2006
Posts: 1436
Location: Bangalore,India
|
|
|
|
Bhagyalakshmi,
| Quote: |
| No primary key and no unique constraint. |
What kind of DB2 table is this? I suggest you go through some reference material for basics. |
|
| Back to top |
|
|
agkshirsagar
Active Member
Joined: 27 Feb 2007
Posts: 691
Location: Earth
|
|
|
|
Assuming table name as SIMPLE-
| Code: |
1. CREATE VIEW SIMP_VIEW AS
SELECT eno, ename, addr, ROW_NUMBER() OVER(PARTITION BY eno,ename,addr ) AS ROW_NUM FROM SIMPLE
2. DELETE FROM SIMP_VIEW WHERE ROW_NUM > 1 |
Murali,
I guess we can create a table without primary key. Nobody creates it though.. |
|
| Back to top |
|
|
murmohk1
Senior Member
Joined: 29 Jun 2006
Posts: 1436
Location: Bangalore,India
|
|
|
|
Abhijit ,
| Quote: |
| I guess we can create a table without primary key. Nobody creates it though.. |
I strongly agree with you. But, if I remeber, you need unique index in lieu of primary key. But original post says -
| Quote: |
| No primary key and no unique constraint. |
|
|
| Back to top |
|
|
chandracdac
New User
Joined: 15 Jun 2007
Posts: 92
Location: bangalore
|
|
|
|
i think it is not possible to create table without primary key . may i know how u created that table. pri key is must because we have to satisfy certain constraints.
thanks
chandra |
|
| Back to top |
|
|
murmohk1
Senior Member
Joined: 29 Jun 2006
Posts: 1436
Location: Bangalore,India
|
|
|
|
Chandra,
| Quote: |
| i think it is not possible to create table without primary key |
Please not that it is NOT true. As I said before, with unique index you should able to create the table. |
|
| Back to top |
|
|
agkshirsagar
Active Member
Joined: 27 Feb 2007
Posts: 691
Location: Earth
|
|
|
|
from db2 v 8 for z/os guide-
| Quote: |
| A table that is to be a parent of dependent tables must have a primary or a unique key—the foreign keys of the dependent tables refer to the primary or unique key. Otherwise, a primary key is optional. |
It doesn't say that you require a primary key/unique index to create an independent table. |
|
| Back to top |
|
|
snehalpatel
New User
Joined: 13 Sep 2007
Posts: 37
Location: India
|
|
|
|
| agkshirsagar wrote: |
Assuming table name as SIMPLE-
| Code: |
1. CREATE VIEW SIMP_VIEW AS
SELECT eno, ename, addr, ROW_NUMBER() OVER(PARTITION BY eno,ename,addr ) AS ROW_NUM FROM SIMPLE
|
. |
Hi
I tried the above query.. But it gave -104 ILLEGAL SYMBOL "("
Please suggest me changes needed in above query? Is ROW_NUMBER() correct syntax ? Please Suggest. |
|
| Back to top |
|
|
agkshirsagar
Active Member
Joined: 27 Feb 2007
Posts: 691
Location: Earth
|
|
|
|
| works on DB2 VERSION 9 for z/os, which one are you using? |
|
| Back to top |
|
|
snehalpatel
New User
Joined: 13 Sep 2007
Posts: 37
Location: India
|
|
|
|
| agkshirsagar wrote: |
| works on DB2 VERSION 9 for z/os, which one are you using? |
We work on DB2 version 7 on Z/os. Can similar query be coded for Db2 7 version. Please let me know if an alternative solution exists for this . |
|
| Back to top |
|
|
|
|
