Discussions
Categories
- 197.2K All Categories
- 2.5K Data
- 546 Big Data Appliance
- 1.9K Data Science
- 450.8K Databases
- 221.9K General Database Discussions
- 3.8K Java and JavaScript in the Database
- 31 Multilingual Engine
- 552 MySQL Community Space
- 479 NoSQL Database
- 7.9K Oracle Database Express Edition (XE)
- 3.1K ORDS, SODA & JSON in the Database
- 555 SQLcl
- 4K SQL Developer Data Modeler
- 187.2K SQL & PL/SQL
- 21.4K SQL Developer
- 296.3K Development
- 17 Developer Projects
- 139 Programming Languages
- 293K Development Tools
- 110 DevOps
- 3.1K QA/Testing
- 646.1K Java
- 28 Java Learning Subscription
- 37K Database Connectivity
- 158 Java Community Process
- 105 Java 25
- 22.1K Java APIs
- 138.2K Java Development Tools
- 165.3K Java EE (Java Enterprise Edition)
- 19 Java Essentials
- 162 Java 8 Questions
- 86K Java Programming
- 81 Java Puzzle Ball
- 65.1K New To Java
- 1.7K Training / Learning / Certification
- 13.8K Java HotSpot Virtual Machine
- 94.3K Java SE
- 13.8K Java Security
- 205 Java User Groups
- 24 JavaScript - Nashorn
- Programs
- 468 LiveLabs
- 39 Workshops
- 10.2K Software
- 6.7K Berkeley DB Family
- 3.5K JHeadstart
- 5.7K Other Languages
- 2.3K Chinese
- 175 Deutsche Oracle Community
- 1.1K Español
- 1.9K Japanese
- 233 Portuguese
Delete all data related to a record, but not the record itself.

I'm new to SQL and I really need help with this assignment, I'm not sure how or where to start.
Assignment details:
- Delete all data related to a record, but not the record itself.
- must accept input parm
- There is is RI in the tables so I must delete children first
Answers
-
Hello,
you should give us more information about your tables etc. Most of as have no crystal ball to see things not mentioned in your post ;-)
Regards
Marcus
-
DELETE FROM TABLE_NAME A
WHERE ROWID<>(SELECT MAX(ROWID)
FROM TABLE_NAME B
WHERE A.COLUMN_NAME=B.COLUMN_NAME);
-
You must be look out for Foreign Keys, which will cascade the Delete operation to its Children. But this will require you to delete the Parent record, which you do not want to delete.
Hence, the only option that you are left with is to manually write the Delete statements starting with the Tables that appear at the bottom of the hierarchy and moving upwards.
I did prepare a dynamic solution once, but not sure if you can adopt it for your situation. please check https://forums.oracle.com/thread/2483825 for the provided solution.
-
Delete all data related to a record, but not the record itself.
Not sure by this, If you delete all the data from a record, how the record will be exists.
must accept input parm
delete from emp where empno=&empno;
This will promt you for the input value.
There is is RI in the tables so I must delete children first
By querying the USER_CONS_COLUMNS table you can get the constraint names on a table and then you can act according to it.
-
Hi,
First check the table refrentials. and delete the data from child tables based on key column.
then delete the data from main table.
to pass parameters in query use (:bind Variable)
for ex.
delete from emp where empno = :empno;
Thanks
-
1004386 wrote: DELETE FROM TABLE_NAME A WHERE ROWID<>(SELECT MAX(ROWID) FROM TABLE_NAME B WHERE A.COLUMN_NAME=B.COLUMN_NAME);
That is a potentially dangerous piece of SQL you have provided.
That will likely delete all records from the table.
-
BluShadow wrote: 1004386 wrote: DELETE FROM TABLE_NAME A WHERE ROWID<>(SELECT MAX(ROWID) FROM TABLE_NAME B WHERE A.COLUMN_NAME=B.COLUMN_NAME); That is a potentially dangerous piece of SQL you have provided. That will likely delete all records from the table.
Or maybe, it won't delete any record from the Table if the data in Column is unique, which to me does not look like what OP is looking for.
-
PurveshK wrote: BluShadow wrote: 1004386 wrote: DELETE FROM TABLE_NAME A WHERE ROWID<>(SELECT MAX(ROWID) FROM TABLE_NAME B WHERE A.COLUMN_NAME=B.COLUMN_NAME); That is a potentially dangerous piece of SQL you have provided. That will likely delete all records from the table. Or maybe, it won't delete any record from the Table if the data in Column is unique, which to me does not look like what OP is looking for.
Indeed. Whichever way, it's not safe SQL, relying on ROWID's, and certainly does not do what the OP was asking.
-
I guess ur requirement is something like this. Let sat I have employees table and EMPID is the primary key, Now u wanted to delete all the remaining table which has empid column for an employee not deleting the main employee base table ? Am i Correct, If that is the requirement Here u go:
SELECT 'delete from '||TABLE_NAME||' where '||COLUMN_NAME||'=&emp_id;
' from user_tab_cols where column_name='EMPNO' and table_name<>'EMP';
The above select lists u the delete statements that u need to perform. U need to modify the column name nad table name value in the aboce query for ur requirement.