-3

Have a difficulties with query. I am new in SQL so trying to make better query for deleting a trip.

Does anyone know how to make it easier in one query?

Thank you

Kelly
  • 3
  • 2
  • You got a couple downvotes, in the future a bit more detail about your environment is helpful. It's a bit hard to determine your intent with the bare minimum of details given. That said, I think I know what you're intent here is. – RThomas Aug 26 '20 at 06:06
  • 1
    The other thing that will help your question get more attention is to specify a specific flavor of SQL. The answer I provided for example won't work across all SQL languages. But I'll use Microsoft SQL Server as an example. – RThomas Aug 26 '20 at 06:10

1 Answers1

1

If you want to perform a delete across all your tables in one single statement and you happen to be using a SQL Engine that support CASCADE you might be in luck.

From your sample query it appears that all tables share a chained PK\FK relationship.

  • Venues through checkpoint_id
  • Checkpoints through day_id
  • Days through trip_id
  • Trips (at the top)

My inference is that TRIP is the top of the chain. If you specifically define the PK\FK relationship 'CONSTRAINT' which you should for good DB design, then on each table, you can alter the constraint with the option CASCADE. With that done simply deleting a row from trip will automatically delete all other rows below via the defined relationship.

In other words, with CASCADE and a SQL ENGINE that supports it - you can simply issue the last delete statement and the rest is implied.

DELETE FROM trips WHERE id = 27

Check table constraint options here. Pay attention to CASCADE definition. Similar question with explanations here.

RThomas
  • 10,702
  • 2
  • 48
  • 61
  • Hello. Thank you for the answer. We have travel app and I am trying to delete my trips. We have five tables as you can see. I created query but my way to solve it is just not professional. – Kelly Aug 27 '20 at 02:08