Oracle SQLPlus: Using single DML statements. Delete all the tuples from all the

Question

Oracle SQLPlus:

Using single DML statements. Delete all the tuples from all the tables pertaining to the movie ‘When Harry Met Sally’

i have tried using the following syntax but i get error: command not properly ended at line 2 "LEFT JOIN ROLE AS mr" any help is appreciated, also below is the contents of the table

DELETE FROM MOVIE
LEFT JOIN ROLE AS mr
ON MOVIE.Id = ROLE.m_Id
LEFT JOIN STAR
ON mr.s_Id = STAR.Id
WHERE MOVIE.Title = 'When Harry met Sally' ;

s_id refers to talbe star

m_id refers to table movie

Movie title Castawa You Got Mail City of Angels budget 90000000 65000000 66000000 25000000 id rating PG-13 PG PG-13 PG-13 genre Drama Romance Horror Romance 4 When Harry Met Sall Role m id character name Chuck Noland Joe Fox Kathleen Kell Maggie Rice Cassiel Seth Sall Harr 2 2 4 2 4 Star Salary 8000000 10500000 15000000 7000000 id name Tom Hanks Meg Ryan birth year 1956 1961 1964 1948 gender Male Female Male Male Nicolas Cage 4 Billy Crystal

Explanation / Answer

we can't use joins to delete the records from multiple tables.

If we want to delete all the correcsponding records of other talbles when we delete from main table we need to use ON DELETE CASCADE with foreign key constraint.

Just update the foreign key constraints with ON DELETE CASCADE then with simple delete query you can delete the corresponding row in all the other tables also.

After these then to delete the movie from moview table

Now with this delete query this movie data is also got deleted in Role and Artist tables because of ON DELETE CASCADE constraint

Related Questions

Navigate

• Browse (All)
• Browse O
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.