FAQ: Manipulation - Update

This community-built FAQ covers the “Update” exercise from the lesson “Manipulation”.

Paths and Courses
This exercise can be found in the following Codecademy content:

Web Development
Data Science

Learn SQL

FAQs on the exercise Update

Join the Discussion. Help a fellow learner on their journey.

Ask or answer a question about this exercise by clicking reply (reply) below!

Agree with a comment or answer? Like (like) to up-vote the contribution!

Need broader help or resources? Head here.

Looking for motivation to keep learning? Join our wider discussions.

Learn more about how to use this guide.

Found a bug? Report it!

Have a question about your account or billing? Reach out to our customer support team!

None of the above? Find out where to ask other questions here!

are commands lines interchangeable? ex:
instead of :
SET twitter_handle = ‘@taylorswift13
WHERE id = 4;

could we use :
WHERE id = 4
SET twitter_handle = ‘@taylorswift13’;

11 Likes

What if there are two rows with the same value for id?

1 Like

There is a bug in exercise 8/11:

New twitter_handle column from exercise 7 is automatically removed when you try to run exercise 8

Refreshing the URL and pressing on the solution button right away works

Hi, I have a question about this exercise.
When we are told to add:

UPDATE celebs 
SET twitter_handle = '@taylorswift13' 
WHERE id = 4; 

SELECT * FROM celebs;

Why can’t I do instead of id for example `WHERE name = ‘Taylor Swift’

Will I always have an id in my tables and I will always use it in these situations?

can you guarantee that name is unique? using name in where clause might update multiple rows

always? No. But identifiers (id) are quite common, and are commonly used when you want to update a single row.

terms like always are tricky in programming. Furthermore its important to realize why you do things are certain way. What the reasons are.

4 Likes

If I want to update each row on my column Twitter, Do I have to create each sentence for every modification?
There is not a way to do all together as on the insert values?.. I was trying in different ways but I couldn’t find a way! Who can please help me?

Not an a simple elegant way, as shown here:

https://stackoverflow.com/questions/25674737/mysql-update-multiple-rows-with-different-values-in-one-query/25674827

1 Like

Since we have already defined the twitter_handle as text , if we run a code as

UPDATE celebs
SET twitter_handle = '34'
WHERE id = 4;

SELECT * FROM celebs;

Will it allow entry to be made as integer . In my case it is yes… How can we decide that we will receive a error if somebody made wrong entry ?

Sql has strict mode for this:

https://dev.mysql.com/doc/refman/8.0/en/sql-mode.html#sql-mode-strict

although often in a real life application, you might do this validation at application level (as oppose to database level)

3 Likes

Both rows will be updated.

That’s why it’s important to make sure id uniquely identifies each row, by setting it to be PRIMARY KEY (see this lesson).

When we call the “WHERE id = 4” statement, does it need to look through the whole table to find where id = 4? I would imagine the runtime of this statement would be O(n). Or is “id” a special case, like searching by index in an array?
Thanks so much :slight_smile:
– confused person from object-oriented background

databases include the ability to create indexes on column, this will speed up the run time. If there is index on the column, the run time is very likel O(n). But i am sure this can be found somewhere on the internet if you want to get into more depth on this matter.

How do you update multiple rows without writing multiple UPDATE clauses?

you use a where that will select multiple rows:

UPDATE celebs
SET twitter_handle = '34'
WHERE id < 100;

Why do we need to select all the columns in the table after we have already updated it? Simply put, why do we need the last line of code “select * from celebs;”

update celebs
set twitter_handle = ‘@taylorswift13
where id = 4;

select * from celebs;

I answered my own question. The purpose is to show the results of the code we just executed.

Is there a shorter code to update multiple rows of the same column?

UPDATE friends
SET email = ‘jane@codecademy.com’
WHERE id = 1;

UPDATE friends
SET email = ‘pw@gmail.com’
WHERE id = 2;

UPDATE friends
SET email = ‘ray@gmail.com’
WHERE id = 3;

Thank you

Not directly in SQL as far as I know.

1 Like