Once you’ve created your tables, and inserted lots of entries, there will come a time when you want to change some things. More specifically, you can: change the structure of a table, or change the values of an entry.
Altering the Structure
When it comes to updating or changing the structure, we call it altering instead. Not surprisingly, every query related to this starts with the ALTER keyword.
Adding Columns
To add a column to a table, use
ALTER TABLE tableName ADD column_name type attributes;
-- Adds a new column, on top of id, username and email
ALTER TABLE accounts ADD password VARCHAR(255);
| id | username | password | |
| 1 | Panda | [email protected] | 1234 |
| 2 | Ginty | pikapika | |
| 3 | Juan | [email protected] | butterfree |
| 4 | Gandalf | flyyoufools |
Removing Columns
To remove a column from a table, use
ALTER TABLE tableName DROP COLUMN column_name;
-- We noticed users don't like giving us their email, so we remove that column
ALTER TABLE accounts DROP COLUMN email;
| id | username | password |
| 1 | Panda | 1234 |
| 2 | Ginty | pikapika |
| 3 | Juan | butterfree |
| 4 | Gandalf | flyyoufools |
Modifying – Changing Column Type
To modify an already existing column means two things: changing the type, or changing the name. Unfortunately, the syntax is slightly different across different database systems.
ALTER TABLE tableName ALTER column_name SET type attributes;SQLite
PostgreSQL
ALTER TABLE tableName ALTER COLUMN column_name type attributes;SQL Server
MS Access
ALTER TABLE tableName MODIFY COLUMN column_name type attributes;MySQL
Oracle (<10G)
ALTER TABLE tableName MODIFY column_name type attributes;Oracle (>=10G)
-- Additionally, we noticed users started taking unnecessarily long usernames, so we're going to change that column to only accept shorter strings.
ALTER TABLE accounts MODIFY COLUMN username VARCHAR(12);
| id | username | password |
| 1 | Panda | 1234 |
| 2 | Ginty | pikapika |
| 3 | Juan | butterfree |
| 4 | GandalfTheGr | flyyoufools |
Modifying – Changing Column Name
Changing the name of an already existing column is much easier, simply use
ALTER TABLE tableName RENAME old_column TO new_column;
-- And we thought the word secret_spy_name sounded cooler than username ALTER TABLE accounts RENAME username TO secret_spy_name;
| id | secret_spy_name | password |
| 1 | Panda | 1234 |
| 2 | Ginty | pikapika |
| 3 | Juan | butterfree |
| 4 | GandalfTheGr | flyyoufools |
Updating Entries
Changing entries, as opposed to altering table structure, is usually called updating an entry. Therefore, such queries always start with the UPDATE keyword.
The syntax for updating entries – you can update multiple entries at the same time – is
UPDATE tableName
SET (column_name = new_value, …, column_name = new_value)
WHERE condition;
Including the WHERE clause is very important, because if you don’t, SQL will just update all the entries with the new information. For the rest, it works exactly the same as in the SELECT statement – “Update this table by setting these columns to these values, where a row meets a certain condition”
-- User Panda forgot his password, again, so we send him a password reset email, and temporarily reset his password to "whatislovebabydonthurtme"; UPDATE accounts SET (password = "whatislovebabydonthurtme") WHERE secret_spy_name = "Panda";
| id | secret_spy_name | password |
| 1 | Panda | whatislovebabydonthurtme |
| 2 | Ginty | pikapika |
| 3 | Juan | butterfree |
| 4 | GandalfTheGr | flyyoufools |