|
View previous topic :: View next topic
|
| Author |
Message |
GauravKudesiya
Warnings : 1
New User
Joined: 11 Oct 2008
Posts: 74
Location: Chicago, IL
|
|
|
|
Hello All,
I need to delete record corrosponding primary key from a table A and all the corrosponding records for that primary key from another table B (Primary key of Table A).
As That key is Primary for Table A , obviously that table has only 1 record, but table B hase many records for that key.
Input is taken from a flat file which contains only the KEY.
To Delete from table B, what steps i have to follow, like
1) declare a cursor (Select ____ from B where key = :Variable)
2) OPEN that Cursor
3) Fetch
Now how to delete the Record which is fetched, or i need to use DELETE in the CURSOR, and it will delete teh record at the tiem of FETCH.
Regards, |
|
| Back to top |
|
 |
guptae
Moderator
Joined: 14 Oct 2005
Posts: 1208
Location: Bangalore,India
|
|
|
|
Hello Gaurav,
Why you want to open the cursor fetch it & then delete the record why dont directly delete using
| Code: |
| DELETE from B where key = :Variable |
|
|
| Back to top |
|
|
GauravKudesiya
Warnings : 1
New User
Joined: 11 Oct 2008
Posts: 74
Location: Chicago, IL
|
|
|
|
I am not sure wether it will work as there are number of records corrosponding to the key.
| Quote: |
| As That KEY is Primary for Table A , obviously that table has only 1 record, but table B has many records for that KEY. |
Regds, |
|
| Back to top |
|
|
guptae
Moderator
Joined: 14 Oct 2005
Posts: 1208
Location: Bangalore,India
|
|
|
|
| Quote: |
| I am not sure wether it will work as there are number of records corrosponding to the key. |
So .... |
|
| Back to top |
|
|
GauravKudesiya
Warnings : 1
New User
Joined: 11 Oct 2008
Posts: 74
Location: Chicago, IL
|
|
|
|
What I have to code?
Delete Query in cursor, So how will it delete the records?
This is my confusion :
At the time of OPEN all records will get deleted or I have to FETCH each record untill last, and record will get deleted at the time of FETCH?
Rgds, |
|
| Back to top |
|
|
dbzTHEdinosauer
Global Moderator
Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne
|
|
|
|
Gaurav,
Ekta is saying, simply DELETE the rows with simple DELETE SQL,
don't use a CURSOR. you can get the count of deleted rows from SQLERRCD(3) from the SQLCA. |
|
| Back to top |
|
|
GauravKudesiya
Warnings : 1
New User
Joined: 11 Oct 2008
Posts: 74
Location: Chicago, IL
|
|
|
|
Thanks For correcting me on the approach.
I will follow that approach...
Will get back here after following 
Regards, |
|
| Back to top |
|
|
sushanth bobby
Senior Member
Joined: 29 Jul 2008
Posts: 1020
Location: India
|
|
|
|
GauravKudesiya,
| Quote: |
I need to delete record corrosponding primary key from a table A and all the corrosponding records for that primary key from another table B
|
This can be done by altering the child table with
referencing the base table.
Sushanth |
|
| Back to top |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006
Posts: 19244
Location: Inside the Matrix
|
|
|
|
DBZ,
Cool new avatar. . .
Ich werde ein Bier mit Jhnen. . . 
d |
|
| Back to top |
|
|
GauravKudesiya
Warnings : 1
New User
Joined: 11 Oct 2008
Posts: 74
Location: Chicago, IL
|
|
|
|
Hi Sushanth,
Can you explain a bit more on this..
Regards, |
|
| Back to top |
|
|
dbzTHEdinosauer
Global Moderator
Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne
|
|
| Back to top |
|
|
sushanth bobby
Senior Member
Joined: 29 Jul 2008
Posts: 1020
Location: India
|
|
|
|
GauravKudesiya,
Its a property of referential constraint, when a row is deleted in the parent table(tableA), the corresponding rows in the child table(tableB) will be deleted automatically.
For this childtable(tableB) needs to be altered.
Sushanth |
|
| Back to top |
|
|
|
|
