WebDevelopersNotes logo

home-icon Home / MySQL / My SQL tutorial – Updating records

My SQL tutorial – Updating records

My SQL tutorial - Updating records

The SQL UPDATE command updates the data in tables. Its format is quite simple.

UPDATE table_name SET
column_name1 = value1,
column_name2 = value2,
column_name3 = value3 ...
[WHERE conditions];

Obviously, like other SQL commands, you can type in in one line or multiple lines.

Let’s look at some examples.
Bignet has been doing good business, the CEO increases his salary by $20000 and perks by $5000. His previous salary was $200000 and perks were $50000.

UPDATE employee_data SET 
salary=220000, perks=55000 
WHERE title='CEO';

Query OK, 1 row affected (0.02 sec)
Rows matched: 1  Changed: 1  Warnings: 0

You can test this out by listing the data in the table.

select salary, perks from   
employee_data WHERE
title = 'CEO';

+--------+-------+
| salary | perks |
+--------+-------+
| 220000 | 55000 |
+--------+-------+
1 row in set (0.00 sec)

Actually, you don’t need to know the previous salary explicitly. You can be cheeky and use arithmetic operators. Thus, the following statement would have done the same job without us knowing the original data beforehand.

UPDATE employee_data SET
salary = salary + 20000,
perks = perks + 5000
WHERE title='CEO';

Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

Another progressive (???) step Bignet takes is changing the titles of Web Designer to Web Developer.

mysql> update employee_data SET
    -> title = 'Web Developer'
    -> WHERE title = 'Web Designer';
Query OK, 2 rows affected (0.00 sec)
Rows matched: 2  Changed: 2  Warnings: 0

It’s important that you take a long hard look at the condition part in the statement before executing update, else you might update the wrong data. Also, an UPDATE statement without conditions will update all the data in the column in all rows!
Be very careful.

Sponsored Links

Your comments
Star icon IMPORTANT Have a question / problem? Click here to ask an expert.

Sponsored Links

Tips

How do I view a deleted web page?
Is there a way to view a deleted web page - one that is no longer available? Yes there is and the solution is quite simple. [more...]

Fact

Tetris was the very first entertainment software to be imported by the United States from the USSR. In 1987, three years after it's launch in the Soviet Union, the game was released in the United States by Spectrum Holobyte for the IBM PC and Commodore 64, the largest selling computer of all time. Tetris was developed by Alexey Pajitnov, a Russian computer programmer, when he was working for the Dorodnicyn Computing Centre of the Academy of Science. One of his responsibilities was testing the capabilities of new hardware. And for this, Pajitnov came up with the idea of designing a game. Since he was working on the Electronika 60, a text-only terminal, the original Tetris game had letters instead of graphics. The game proved to be a hit with his colleagues and soon became popular in the Soviet Union. Tetris then found its way to Budapest, Hungary, and was picked up by Spectrum Holobyte who developed a version of it and started to market it in the United States. [more...]

We use cookies to give you the best possible website experience. By using WebDevelopersNotes.com, you agree to our Privacy Policy