How SQL Update Statement function

The UPDATE statement is used to update existing records in a table. General syntax:

UPDATE table_name
  SET column1=values
       where some_column=some_values;

In this part of the tutorial we will use the table Test that has the following structure and data :

SQLselect * from test;
ID   NAME          EMAIL
----------  --------------------
1   Eve             [email protected]
2   Jon             [email protected]
3   Mike

-we can see that Mike has no email address and Paul has no id and email address. -so for us to insert an email address that will be on the same row with Mike we will use the "update" command with the "where" clause. -before we do this create the table and add data to it using the following sql sintax: Script:(copy and paste this in your mysql terminal)

--first lets drop the table Test (if she exists, if not skip the drop line).Drop table test;
--we create the table;
create table test (id int,name varchar2(20),email varchar2(20));
--we insert values into our table .
insert into test values (1,'Eve','[email protected]');
-- insert with no condition ( insert all values)
insert into test values (2,'Jon','[email protected]');
-- insert with no condition ( insert all values)
insert into test(id,name) values (3,'Mike');
-- insert with no condition ( insert only declared values)
insert into test(name) values ('Paul');
-- insert with no condition ( insert only declared values)

Now see the result :

Sql :select * from test;
-  It should be like
ID   NAME          EMAIL
----------  --------------------
1   Eve        [email protected]
2   Jon        [email protected]
3   Mike
4   Paul

So now let's create the update statement that will add an email address to Mike

• We can do this using as 'where' reference our ID column

Example:

SQL
update test set email ='[email protected]' where id=3;

Check the table

SQL
select * from test;
    ID   NAME         EMAIL
 ---------- -------------------- --------------------
     1  Eve            [email protected]
     2  Jon            [email protected]
     3  Mike          [email protected]
     4  Paul

We can see that the table added an email address where ID column is equal to 3.

• Now let us add an id and an email address to the row where Name is Paul:

Example:

SQLupdate test set id=4
   email='[email protected]'
      where name='Paul';

Great, now check the table.

SQLselect * from test;
ID   NAME         EMAIL
---------- -------------------- --------------------
1  Eve            [email protected]
2  Jon            [email protected]
3  Mike          [email protected]
4  Paul           [email protected]

We can see that Paul got his ID and his EMAIL field in. Note: The "set" statement is the one responsible for pointing which columns will be updated and with what values they will receive and the "where" statement will point which is the row/rows will be updated. Very important If you omit the "where" clause, all records on that column will be updated!So always use a where clause and double check you update statements before running them. Example of update statement with no "where " clause:

SQLupdate test set id=4 , email='[email protected]' ;

And let's check the changes made by this statement.

SQL
select * from test;
    ID NAME       EMAIL
 -------- ---------------- --------------------
     4 Eve         [email protected]
     4 Jon         [email protected]
     4 Mike        [email protected]
     4 Paul         [email protected]

We can see the when we used the "update" statement with no "where" clause all the values of the column ID and EMAIL will receive the values stated in the update statement. Se be aware of updates with no condition clauses they can be dangerous. There's lot's of update condition clauses types, but for now we will stick with this only. More advanced updates types in the future tutorials.