|
View previous topic :: View next topic
|
| Author |
Message |
Rishi Trehan
New User
Joined: 14 Feb 2012
Posts: 5
Location: USA
|
|
|
|
A team member accidentally deleted all records from a table using DSNTIAUL. I wish to prevent this in future. So far I have tried to use -
SOLUTION 1 -
DELETE FROM tablename
WHERE key IN ( SELECT key FROM tablename
WHERE key = 0002000100
FETCH FIRST 3 ROWS ONLY );
but I got an error -
DSNT408I SQLCODE = -4700, ERROR: ATTEMPT TO USE NEW FUNCTION BEFORE NEW
FUNCTION MODE
Looks like although we have DB2 V9, it is still not in New Function Mode.
SOLUTION 2 - Creating a view -
CREATE VIEW view1 AS
SELECT key, ROW_NUMBER() OVER (PARTITION BY key) RN
FROM tablename;
SELECT FROM view1
WHERE key = 0002000100
AND RN=3;
But I get an error -
SQL0104N An unexpected token "(" was found following "". Expected tokens may include: ", FROM INTO". SQLSTATE=42601
----------------------------------------------------------------------------------
I am fancying that there could be a way to check the value of SQLERRD(3) after the execution of the Delete statement. If the count is not what you expect then issue a Rollback.
Any ideas?
Regards |
|
| Back to top |
|
 |
chandan.inst
Active User
Joined: 03 Nov 2005
Posts: 275
Location: Mumbai
|
|
|
|
Hi Rishi..
What is your exact requirement? How may records you want to delete?
You can use SQLERRD(3) to check how may no of records deleted in COBOL but I am not sure about DSNTIAUL.
Also your Subquery from Solution 1 will always return key 0002000100 as it is in where clause and in turn it will delete all records from table with key =0002000100.
How are you going to prevent deleting of records by creatinga VIEW?
Plase elaborate your requirement.
Regards,
Chandan |
|
| Back to top |
|
|
Nic Clouston
Global Moderator
Joined: 10 May 2007
Posts: 2455
Location: Hampshire, UK
|
|
|
|
| chandan.inst wrote: |
What is your exact requirement? How may records you want to delete?
|
How about reading and UNDERSTANDING the very first line of the topic. Read EVERY word. Then go away and blush and come back and say 'Ooops'. |
|
| Back to top |
|
|
chandan.inst
Active User
Joined: 03 Nov 2005
Posts: 275
Location: Mumbai
|
|
|
|
Hi Nic,
I read the thread and understood the requirement.
I asked for the exact requirement after going through the below solution provided
SOLUTION 1 -
| Code: |
DELETE FROM tablename
WHERE key IN ( SELECT key FROM tablename
WHERE key = 0002000100
FETCH FIRST 3 ROWS ONLY ); |
With this all rows with key = 0002000100 will be deleted but subquery selects only first 3 rows
So I guess requirement is to delete only 3 rows which is not happening.
So just wanted to confirm the assumption what is the basic criteria for deleting the records
Regards,
Chandan |
|
| Back to top |
|
|
Nic Clouston
Global Moderator
Joined: 10 May 2007
Posts: 2455
Location: Hampshire, UK
|
|
|
|
| The requirement is to stop someone from accidentally deleteing ALL rows in the table as has happened. |
|
| Back to top |
|
|
|
|
