UPDATE queries are another necessary component of any database management system – in this blog, we walk you through them. Join us!
UPDATE queries are a necessary part of every application. Everyone needs to update data once in a while – everyone makes mistakes, right?
Update queries are just that – they’re queries that help developers update their data. In this blog we’re walking you through them as well.
In its most basic form,
UPDATE queries look something along those lines:
Do note that by default,
UPDATE queries must have only the
SET clause – however, additional parameters can also be included, these being:
The most likely clause you’re going to encounter is
WHERE, followed by the
IGNORE clause. The priority clauses are used very rarely and if they’re used, they’re usually used in environments where there are a lot of queries running simultaneously and developers need to update data in chunks.
UPDATE Query From the Inside
UPDATE queries look like from the inside (to learn how to profile a query, turn back to the second or first part of these tutorials):
We can see that there are multiple things going on:
UPDATE queries, everything works in a simple fashion – we must have sufficient permissions, InnoDB will create row-level locks in the process, update the data, and end.
Everything may look relatively simple from the outset, but when we dig into the processes we quickly see that everything’s a little more complex than we could imagine.
Advanced DBAs will know that both indexes and partitions slow
UPDATE queries down because of the fact that data existing in indexes and partitions need to be updated together with the data itself. That’s not it –
UPDATE queries can also update multiple columns at once if we run them by specifying multiple columns like so:
Do note that we specified multiple columns after the “,” sign before the
WHERE clause. There’s a downside – when updating a lot of data such queries will definitely take up a lot of time, so there are a couple of other tricks we can pull from our sleeves: the first one is using the
IGNORE keyword to ignore all of the errors, but that’s not it either – did you know that the
DEFAULT keyword can also be used to update bigger data sets? Yes, that‘s not an usual practice, but the
DEFAULT statement can also double as an
UPDATE statement in very specific cases:
Now consider the same scenario, just with the DEFAULT keyword in use:
The reason why our DBMS behaved in this way is pretty self-explanatory: some of the fields have already been filled-in prior to inserting the data. We shot two rabbits with one shot: the file was smaller since we didn’t specify other values (think of how much space we could save if there would’ve been billions of records?), and we didn’t have to run an
UPDATE query after the
LOAD DATA INFILE query – the values were already filled in by the database itself.
Neat, right? Now quick – grab a free trial of DbVisualizer and start being in charge of your database!
In this blog, we have walked you through the third query in the CRUD acronym – the
UPDATE query that’s used to update data within tables. We bet you didn’t know that the
DEFAULT keyword can also be used to update data!
Follow us on Twitter, read our blog to stay updated around what’s happening in the database world, and until next time.
What Does the UPDATE Query Do? Do Indexes Help it?
UPDATE query updates data within a table. Indexes and partitions make it slower at the expense of speeding up