DBA SQL Update – Solution to exercise 1

Let’s answer the first exercise you have been working on (DBA SQL Update exercise).

Before any update, you may use a SQL select query if you are not sure and with even more safety and furthermore if you are in production, then make a dump of your base before applying any update !

Here is the select I propose which will lead to the update :

SELECT * FROM book 
INNER JOIN category ON category.id = category_id 
INNER JOIN store ON category.store_id = store.id 
WHERE store.name = 'Store 2';

And the rest of the rows in the tables if you have run the insertions :

Original data

Solution

Using inner joins :

UPDATE book SET price_coefficient = 1.2 
FROM category INNER JOIN store ON category.store_id = store.id 
WHERE category.id = category_id AND store.name = 'Store 2';
Resulting data

Using sub queries :

UPDATE book SET price_coefficient = 1.2 
WHERE category_id IN 
(SELECT id FROM category WHERE store_id IN 
(SELECT id FROM store WHERE name = 'Store 2'));

Same result with different queries, I save you some alternatives which use a mix of the above ones.

Let’s now check the differences in terms of performance.

Analysis

Actually both queries provide similar performances when you hit the analysis tool of your engine, see for yourself :

Explain analyse of the update - DBA Exercise 1
Postgresql EXPLAIN ANALYSE

Note that I have added an index to improve the performance of the query as we are searching based on the name field, an index on that field is obvious.

CREATE INDEX idx_store_name 
ON store(name);

If you want to learn more about indexing and SQL, check the following resource : Luke.

Don’t hesitate to share your thoughts and concerns on the exercise.

See you at the next one !

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Want more information?

Related links will be displayed within articles for you to pick up another good spot to get more details about software development, deployment & monitoring.

Stay tuned by following us on Youtube.

%d bloggers like this: