Portal | Manuals | References | Downloads | Info | Programs | JCLs | Mainframe wiki | Quick Ref
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Profile Log in to check your private messages Log in
 
Need to delete the three records and keep only one copy

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
Bhagyalakshmi C

New User


Joined: 24 Jul 2007
Posts: 38
Location: Bangalore

PostPosted: Mon Sep 03, 2007 1:08 pm    Post subject: Need to delete the three records and keep only one copy
Reply with quote

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
View user's profile Send private message

murmohk1

Senior Member


Joined: 29 Jun 2006
Posts: 1439
Location: Bangalore,India

PostPosted: Mon Sep 03, 2007 1:19 pm    Post subject:
Reply with quote

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
View user's profile Send private message
agkshirsagar

Active Member


Joined: 27 Feb 2007
Posts: 686
Location: Earth

PostPosted: Mon Sep 03, 2007 2:06 pm    Post subject:
Reply with quote

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
View user's profile Send private message
murmohk1

Senior Member


Joined: 29 Jun 2006
Posts: 1439
Location: Bangalore,India

PostPosted: Mon Sep 03, 2007 2:39 pm    Post subject:
Reply with quote

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
View user's profile Send private message
chandracdac

New User


Joined: 15 Jun 2007
Posts: 92
Location: bangalore

PostPosted: Mon Sep 03, 2007 3:25 pm    Post subject: Reply to: Need to delete the three records and keep only one
Reply with quote

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
View user's profile Send private message
murmohk1

Senior Member


Joined: 29 Jun 2006
Posts: 1439
Location: Bangalore,India

PostPosted: Mon Sep 03, 2007 3:35 pm    Post subject:
Reply with quote

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
View user's profile Send private message
agkshirsagar

Active Member


Joined: 27 Feb 2007
Posts: 686
Location: Earth

PostPosted: Mon Sep 03, 2007 5:18 pm    Post subject:
Reply with quote

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
View user's profile Send private message
snehalpatel

New User


Joined: 13 Sep 2007
Posts: 37
Location: India

PostPosted: Thu Sep 20, 2007 5:08 pm    Post subject:
Reply with quote

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
View user's profile Send private message
agkshirsagar

Active Member


Joined: 27 Feb 2007
Posts: 686
Location: Earth

PostPosted: Thu Sep 20, 2007 5:45 pm    Post subject:
Reply with quote

works on DB2 VERSION 9 for z/os, which one are you using?
Back to top
View user's profile Send private message
snehalpatel

New User


Joined: 13 Sep 2007
Posts: 37
Location: India

PostPosted: Thu Sep 20, 2007 6:09 pm    Post subject:
Reply with quote

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
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2 All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts Inefficient BUILD - VB records paddin... Daniel Prosser SYNCSORT 6 Thu Dec 14, 2017 3:52 pm
No new posts ISSUE IN copying Sequential file reco... thesumitk JCL & VSAM 2 Wed Dec 13, 2017 3:07 pm
No new posts Merging 2 records at multiple rows wi... Bijesh DFSORT/ICETOOL 2 Wed Dec 06, 2017 1:50 am
No new posts Compare and COPY using DFSORT pshongal DFSORT/ICETOOL 3 Fri Nov 17, 2017 9:49 am
No new posts Increment number for every group of r... srajendran2 DFSORT/ICETOOL 7 Thu Nov 09, 2017 10:31 pm

Facebook
Back to Top
 
Job Vacancies | Forum Rules | Bookmarks | Subscriptions | FAQ | Polls | Contact Us