ICON

Help - SQL UPDATE function

Help Center How To Use Our Products? SQL UPDATE function

UPDATE function

5 MIN EXPERT DATA, SQL QUERIES, UPDATE FUNCTION


This functionality is only available in Spotzi Studio.

The UPDATE statement is used if you want to update (multiple) records at once. The UPDATE statement is used with the WHERE statement. Operators are used to apply bulk edits of certain values. An UPDATE statement looks like this:

UPDATE {name of dataset}
SET {column} = {value2}
WHERE {column} = {value1}

Keep in mind! UPDATE statements are only available in your My Data screen
Don’t enter any UPDATE queries into the SQL module of your map. This won’t work. If you want to use an UPDATE query for your data, please go to My Data.

In the example below we have a dataset with 2-digit postal codes of the US. Each postal code area has its own sales manager.

UPDATE Query

Let’s say we want to update the responsible sales manager of area ID number 12362 to James instead of Rick. In that case the UPDATE query will look like this.

UPDATE usa_salesmanagers_2_digit_areas
SET salesmanager='James'
WHERE area_id_number=12362;

UPDATE Query

Now we will do a more advanced UPDATE statement where we only want change the sales manager of the area IDs that start with 17 to Adam. Therefor you need the WHERE clause with the LIKE operator. To apply this update the UPDATE query will look like this.

UPDATE usa_salesmanagers_2_digit_areas
SET salesmanager = 'Adam'
WHERE area_id_number LIKE '17%';

UPDATE Query

Keep in mind! Always use a WHERE clause when using the UPDATE statement
The WHERE clause specifies which record(s) that should be updated. If you use the UPDATE without a WHERE clause, all records in your dataset will be updated!

Operators in the WHERE clause


The WHERE clause and operators are very important when applying an UPDATE statement. In the list below you can find a list of available operators to use with an UPDATE statement.

Operator Description
= Equal
<> Not equal. Note: In some versions of SQL this operator may be written as !=
> Greater than
< Less than
>= Greater than or equal
<= Less than or equal
BETWEEN Between an inclusive range
LIKE Search for a pattern
IN To specify multiple possible values for a column
AND To specify multiple possible conditions separated by AND
OR To specify multiple possible conditions separated by OR
NOT To specify a condition when not true

Related blog posts

Interested in our solutions? Please contact our sales team.

Whether you are new to data analytics or already an expert. We offer solutions for everyone. You don’t need to be a data expert to work with Spotzi Location. For more tailor made solutions we offer Spotzi Studio. With this plan you cannot only manage the data yourself but also connect to our API and manage users for the dashboards you create. Or work with our Market Ready Dashboards and drive on autopilot.