How is ALTER different from UPDATE?

Question

In the context of this exercise, how is ALTER different from UPDATE?

Answer

Although similar in the sense that both statements will modify a table, these statements are quite different.

The ALTER statement is used to modify columns. With ALTER, you can add columns, remove them, or even modify them.

The UPDATE statement is used to modify rows. However, UPDATE can only update a row, and cannot remove or add rows.

36 Likes

Alter can change data inside columns and rows as wel as remove items.
In addition to that it can subtract add or subtract columns which update can not.

UPDATE ONLY CHANGES THE CURRENT INDIVIDUAL RECORDS INSIDE THE TABLES.

8 Likes

Please give example how ALTER can change data inside column AND rows ?

4 Likes

It isn’t so direct with rows but if the data inside a specific column has changed, then the data for a row has changed as well. I hope that makes sense. So it’s more of a overview standpoint.

1 Like

ALTER statement adds a new column to the table
UPDATE statement edits an existing row, irrespective if the row contains a value or not

7 Likes

I think a simple way of putting it is:
ALTER modifies the structure of the table (by adding, removing or renaming columns)
UPDATE modifies the information contained in the table

55 Likes

Is there a shortcut to writing all this out?

UPDATE friends
SET email = ‘[email protected]
WHERE id = 1;

UPDATE friends
SET email = ‘[email protected]
WHERE id = 2;

UPDATE friends
SET email = ‘[email protected]
WHERE id = 3;

4 Likes

Yes, good question. Also want to know how it looks like. It is even interesting that nobody answered that question yet.

Is it because “ALTER” clause can only change types of columns and not the exact values?

Thank you for answering)

Can we do the same with INSERT?

Yes, there are various ways to do so. One of the easiest ways to do so is using CASE. It is just like an if-else statement.

Example -
UPDATE friends
SET email = CASE WHEN id = 1 THEN '[email protected]
WHEN id = 2 THEN ‘[email protected]
WHEN id = 3 THEN '[email protected]
END
WHERE id IN (1, 2, 3);

The other ways are by performing using UPDATE with JOIN or through an INSERT statement.
Though I find these two methods complex. And through CASE is the easiest way to do so.
Here is a link explaining other ways to do it:

Hope it helps. :wink:

3 Likes

We can use INSERT statement for the same. But, you should have atleast one primary key for your table and then you can use DUPLICATE KEY clause along with UPDATE to update the values instead of adding it.

Example -
INSERT INTO Test(gender, id ,title)
VALUES(‘F’, 1, ‘HELLO’), (‘M’, 2, ‘BYE’)
ON DUPLICATE KEY UPDATE gender = VALUES(gender), id = VALUES(id), title = VALUES(title) ;

Here, id is a PRIMARY KEY.
The above statement will update the values of existing records with the values specified using VALUES.
i.e For id = 1, the respective values for gender and title will get updated, and same for id = 2 the respective values will get updated.
Note : The record with id =1 and id = 2 are already existing.
If it does not exist then a new record with specified value will be created.

:grinning:

2 Likes

Your case is with MySQL. Will this work with SQLlite? that is what’s being used in this course.

If I wanted to change the data type would I use UPDATE or ALTER? For example if I had set the data type to VARCHAR but need to change to VARCHAR(100), or change the data type from INT to float?

ALTER is a DDL Command and UPDATE is DML Command.
So, DDL Command do not have Where clause to filter the data , where as DML Commands use where Clause to filter the data.
DDL Commands work Table Structure and DML Commands work on Table Information Like rows

Hi, just tagging on the back of exercise 152, can I just confirm that UPDATE, SET and WHERE has to be in that specific order for it to work? My instinctive logic was “I want to UPDATE this table WHERE id is X and SET this field to X”.

I believe the exercise RUN only worked when I used UPDATE, SET, WHERE, and did not RUN when I ordered it in UPDATE, WHERE, SET.

  1. ALTER statement is used to modify the structure of a database object such as a table, view, or index, whereas UPDATE statement is used to modify the data in the table.
  2. The ALTER statement can be used to add or remove columns, change data types of columns, rename tables or columns, and modify constraints, among other things. UPDATE statement, on the other hand, can be used to modify one or more rows in a table by changing the values of one or more columns.
  3. ALTER statement affects the entire table and may require additional operations such as data migration, index rebuilding, or table recreation, whereas UPDATE statement only affects the specific rows and columns that are being modified.
  4. ALTER statement is a DDL (Data Definition Language) statement, while UPDATE statement is a DML (Data Manipulation Language) statement.
2 Likes

@yasminebb changing data type (a column attribute) I believe you would use ALTER. ALTER changes columns and UPDATE changes rows is how I am remembering these.

@arc4684762678 SQL is very specific like that. I remember taking a course where the general syntax is shown to be very rigid.

There’s six main operations and the order to be followed are:

SELECT, FROM, WHERE, GROUP BY, HAVING, and ORDER BY

Hope this helps!

1 Like

Thank you brother, well said. ALTER is for modifiing the strcuture and UPDATE is for modifing information inside the structure.
But if we want to add rows we can use INSERT INTO statement.

is it becoz the rows have a unique identifier as the Primary key? or am I missing the plot lol