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 :

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';

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 :

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 !