(click anywhere to close)
OPEN MENU

[SQL] Updating

category: Website | course: SQL | difficulty:

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); 
idusernameemailpassword
1Panda[email protected]1234
2Gintypikapika
3Juan[email protected]butterfree
4Gandalfflyyoufools

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; 
idusernamepassword
1Panda1234
2Gintypikapika
3Juanbutterfree
4Gandalfflyyoufools

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

idusernamepassword
1Panda1234
2Gintypikapika
3Juanbutterfree
4GandalfTheGrflyyoufools

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;

idsecret_spy_namepassword
1Panda1234
2Gintypikapika
3Juanbutterfree
4GandalfTheGrflyyoufools

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

idsecret_spy_namepassword
1Pandawhatislovebabydonthurtme
2Gintypikapika
3Juanbutterfree
4GandalfTheGrflyyoufools
CONTINUE WITH THIS COURSE
Do you like my tutorials?
To keep this site running, donate some motivational food!
Crisps
(€2.00)
Chocolate Milk
(€3.50)
Pizza
(€5.00)