|
View previous topic :: View next topic
|
| Author |
Message |
Anand Kumar
New User
Joined: 29 Aug 2007
Posts: 24
Location: chennai
|
|
|
|
Hi,
I have a requirement in a COBOL/DB2 program such that if the primary key columns are present we need to update the table else we need to insert a record. Is it possible in a single statement.
If so can you please give me an example!! I have given the insert query below. DAY_KEY & MONTH_KEY are the primary keys of GWEXP table
| Code: |
EXEC SQL
INSERT INTO GWEXP
(DAY_KEY,MONTH_KEY,BIED_RNT_PCT,LAAT_RNT_PCT)
VALUES (:DCLGWDAT03.DAG-KEY,
:W-MONTH-KEY-EUR ,
:DCLRENTEPERC-CALL.PERC-BD,
:DCLRENTEPERC-CALL.PERC-LD)
END-EXEC |
Thanks,
Anand |
|
| Back to top |
|
 |
sushanth bobby
Senior Member
Joined: 29 Jul 2008
Posts: 1020
Location: India
|
|
|
|
Anand,
Get to know what version of DB2 you are using. If its DB2 V9, MERGE statement is what you are looking for. BLACKLE it.
Sushanth |
|
| Back to top |
|
|
dbzTHEdinosauer
Global Moderator
Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne
|
|
|
|
| deleted by poster |
|
| Back to top |
|
|
Anand Kumar
New User
Joined: 29 Aug 2007
Posts: 24
Location: chennai
|
|
|
|
We are using 8.1  |
|
| Back to top |
|
|
sushanth bobby
Senior Member
Joined: 29 Jul 2008
Posts: 1020
Location: India
|
|
|
|
Anand,
Start programming...
Happy Weekend,
Sushanth |
|
| Back to top |
|
|
MFRASHEED
Active User
Joined: 14 Jun 2005
Posts: 186
Location: USA
|
|
|
|
| You would DELETE the row first and then INSERT it. |
|
| Back to top |
|
|
dbzTHEdinosauer
Global Moderator
Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne
|
|
|
|
| Quote: |
You would DELETE the row first and then INSERT it.
|
That would be a backwards approach.
depends on the statistical chance of the row already existing.
If a majority of the 'update' requests
(the trigger or transaction file driving the process)
would result in an update to an existing row,
then simply UPDATE and if a NOFIND (+100), then INSERT.
on the other hand
If a majority of the 'update' requests
(the trigger or transaction file driving the process)
would require an INSERT,
then simply INSERT and if a DUP-ROW (-803), then UPDATE.
and a last point:
To always DELETE first and then INSERT would in effect use more space.
I don't believe that an INSERT would reuse a DELETEd row's space.
(but there are others here that do know and can correct me) |
|
| Back to top |
|
|
|
|
