several MYSQL statements in one

158
December 08, 2017, at 08:01 AM

I have a place ('place') where I can have some boxes ('box'). In each box, I can have items ('item'). I can also have items in the place outside of a box. I want to delete a place, and by so, to delete each boxes in the place, including items in each boxes as well as the items in the place but outside any box. I got it working with the following separated statements:

1: select the place

SELECT placeid, placename 
FROM place 
WHERE userid = (SELECT id 
                FROM user 
                WHERE username = 'username' AND password ='password') 
  AND placename = 'placename'

2: select the boxes in that place

SELECT boxid, boxname 
FROM box 
WHERE placeid = 'id'

3a: for each box, delete the items in it

DELETE FROM item 
WHERE boxid = 'boxid'

3b: then delete the box

DELETE FROM box 
WHERE id = 'boxid'

4: delete the items in the place not in a box

DELETE FROM item 
WHERE placeid = 'placeid' AND boxid = 0

5: delete the place

DELETE FROM place 
WHERE id = placeid

This is working but of course this is slow. The error management is also not that nice to work with.

Is there a way of doing all this with fewer SQL statements or even only one?

I got of course the same question for deleting a box in one statement!

edit: mysql with php

Thanks a lot.

Answer 1

Because item and box both have a placeid (per the info in your question) you can simplify this task a bit, but you still need 3 DELETE statements:

SELECT p.placeid
INTO #PlacesToDelete
FROM place p 
INNER JOIN [user] u ON p.userid = u.id
WHERE u.username = 'username'
    AND u.[password] = 'password'
    AND p.placename = 'placename'
DELETE i
FROM item i
INNER JOIN #PlacesToDelete ptd ON i.placeid = ptd.placeid
DELETE b
FROM box b
INNER JOIN #PlacesToDelete ptd ON b.placeid = ptd.placeid
DELETE p
FROM place p
INNER JOIN #PlacesToDelete ptd ON p.placeid = ptd.placeid

Or, if this is related to MySQL, you should be able to further simplify and use a single statement:

DELETE p, b, i
FROM place p 
INNER JOIN [user] u ON p.userid = u.id
INNER JOIN box b ON b.placeid = p.placeid
INNER JOIN item i ON i.placeid = p.placeid
WHERE u.username = 'username'
    AND u.[password] = 'password'
    AND p.placename = 'placename'
Answer 2

You should try ON DELETE CASCADE, it would look like this:

ALTER TABLE box ADD CONSTRAINT fk_box_place_id 
                FOREIGN KEY (placeid) 
                REFERENCES place(id) 
                ON DELETE CASCADE;
ALTER TABLE item ADD CONSTRAINT fk_item_place_id 
                 FOREIGN KEY (placeid) 
                 REFERENCES place(id) 
                 ON DELETE CASCADE;

I don't think you need any other cascades, since placeid is available in all tables and will cover items both in and out of boxes, but maybe I'm overlooking something.

Once set up, all you do is delete the place you want to delete and the rest is handled for you:

DELETE 
FROM place
WHERE placeID = 'deletedPlaceID';
Answer 3

SQL Stored Procedure (SP) might be useful to get better performance, because you have more queries to execute at a time, by normally db engine check the syntax error exist or not for each query, but once you do it with SP, it'll check the whole syntax just creation time, but after you execute these queries via SP it'll never check the syntax error, because it's already ok.

You can also implement index for required fields and delete cascade. Try to summarize them with join, but don't try to use temporary table, its causing for performance.

READ ALSO
Google Cloud App - browser in waiting mode

Google Cloud App - browser in waiting mode

There is probably a very simple solution to my problem, but I'm new to Google Cloud so am struggling with this

212
How to convert JSON column values into separated schema with many rows in SQL Server?

How to convert JSON column values into separated schema with many rows in SQL Server?

I have a data set that has only one column has JSON formatHere is snap from the table:

183
how can i password lock documents and access by password in wordpress

how can i password lock documents and access by password in wordpress

i have a website in wordpress in that a page where i put a search box in that the person have to type password and submit it to view the pdfit wont autocomplete and only view if full password entered else "no document"

133
SQLAlchemy: Query Parent with Efficient Child Search & Filter

SQLAlchemy: Query Parent with Efficient Child Search & Filter

I've seen a few topics that are similar, but can't seem to get any of them to work efficiently

153