2023-10-26 12-18-43
Rui Rodrigues
37Views
5Comments
Solved
Can you do Multiple SQL statements inside a SQL Advance?
Question
SQL
Beginner
Reactive
Database
Traditional Web
Application Type
Traditional Web, Reactive
Service Studio Version
11.54.28 (Build 62889)
Platform Version
11.24.0 (Build 41209)

Hello there,
Can you do multiple SQL stamentents inside a single SQL Advanced?
Example:

DELETE {Student}:
SELECT * FROM {STUDENT};

My goal is that the output shows if the list is Empty or not.

0
0
16 Oct
Solution

Hello @Rui Rodrigues , You sure can. I have tried this to confirm. I have a test entity with 1 row. I have tried 2 pairs of SQL queries.

first case

SELECT * FROM {Book_Details};

DELETE FROM {Book_Details} WHERE {Book_Details}.[Id] = 1;

in this case, first the select query is run and then delete query, thus giving 1 row in output.


second case

DELETE FROM {Book_Details} WHERE {Book_Details}.[Id] = 1;

SELECT * FROM {Book_Details}; 

in this case, first the delete query is run and then select query, thus giving 0 rows in output.


So, we can run multiple SQL statements inside a single SQL Advanced, but the output will get affected by their sequence

1
0
16 Oct

Yes you can do that.

It is easy, and even a best practice to do bulk operations, like a delete in an advanced query

But i suggest make an aggregate to check if {Book_details} is really empty.

0
0
16 Oct

As others said, you can, but I'd consider it bad practice. There's no advantage over splitting the query in two. The code will be more difficult to read, as a single node contains two different concepts.

Also, a DELETE FROM {Student} will either delete all records from Student, or none. Using a SELECT * (btw, never use SELECT * in an OS SQL! At the very least use SELECT {Student}.*!) will either find no records, or all records, but more likely in case of an error the SELECT isn't executed at all, as the database will throw an exception. And why fetch all records if you just want to know whether there are any records?

0
0
16 Oct

Thank you Kilian.
My goal was to reduce the overhead between SQL queries gaining a margin on the duration.
Congratz on the award at One - Lisbon

0
0
16 Oct

Replying to Rui Rodrigues' comment on 16 Oct 2023 12:26:09

I think the overhead would be negligeable, especially because a) if you have a lot of records to delete, that will take the bulk of the time and b) you probably won't very often execute such a delete all records. Sounds a bit like premature optimization :).

0
0
16 Oct
Community GuidelinesBe kind and respectful, give credit to the original source of content, and search for duplicates before posting.