Update Data in a MySQL Database

Upgrade table TABLE_NAME

Updating data

Updating data is a standout amongst the most significant undertakings when you work with the database. In this instructional exercise, you will figure out how to utilize the MySQL UPDATE articulation to refresh information in a table.

Prologue to MySQL UPDATE proclamation

You utilize the UPDATE proclamation to refresh existing information in a table. you can likewise utilize the UPDATE articulation to change segment estimations of a solitary line, a gathering of columns, or all lines in a table.

The accompanying shows the language structure of the MySQL UPDATE proclamation:

{`
UPDATE [LOW_PRIORITY]
[IGNORE] table_name
SET
column_name1 = expr1,
column_name2 = expr2,
...
[WHERE
condition];
`}

In the UPDATE articulation:

In the first place, determine the table name that you need to refresh information after the UPDATE watchword.

Second, the SET condition determines which segment that you need to adjust and the new qualities. To refresh numerous sections, you utilize a rundown of comma-isolated assignments. You supply an incentive in every section's task as a strict worth, an articulation, or a subquery.

Third, determine which lines to be refreshed utilizing a condition in the WHERE statement. The WHERE statement is discretionary. On the off chance that you overlook the WHERE condition, the UPDATE proclamation will refresh all lines in the table.

Notice that the WHERE condition is important to the point that you ought not overlook. Here and there, you might need to change only one column; However, you may overlook the WHERE proviso and inadvertently update all lines of the table.

MySQL bolsters two modifiers in the UPDATE articulation.

The LOW_PRIORITY modifier teaches the UPDATE explanation to defer the update until there is no association perusing information from the table. The LOW_PRIORITY produces results for the capacity motors that utilization table-level bolting just, for instance, MyISAM, MERGE, MEMORY.

The IGNORE modifier empowers the UPDATE proclamation to keep refreshing columns regardless of whether blunders happened. The lines that reason mistakes, for example, copy key clashes are not refreshed.

MySQL UPDATE models

How about we practice the UPDATE articulation with certain tables in the MySQL test database.

Utilizing MySQL UPDATE to adjust esteems in a solitary section model

In this model, we are going to refresh the email of Mary Patterson to the new email mary.patterso@classicmodelcars.com.

To start with, to guarantee the update is effective, we inquiry Mary's email from the representatives table utilizing the accompanying SELECT proclamation:

{`
SELECT
firstname, lastname, email
FROM
 representatives
 WHERE
 employeeNumber = 1056;
 `}

MySQL Update model

Second, we can refresh Mary's email to the new email mary.patterson@classicmodelcars.com utilizing the UPDATE explanation as appeared in the accompanying question:

{`
UPDATE representatives
 SET
 email = 'mary.patterson@classicmodelcars.com'
 WHERE
 employeeNumber = 1056;
 `}

Since we simply need to refresh one column, we utilize the WHERE provision to determine the line utilizing the representative number 1056. The SET statement sets the estimation of the email segment to the new email.

Third, we execute the SELECT articulation again to confirm the change.

{`
SELECT
 firstname, lastname, email
 FROM
 workers
 WHERE
 employeeNumber = 1056;
 `}

MySQL UPDATE table model

Utilizing MySQL UPDATE to alter esteems in different sections

To refresh values in the different segments, you have to determine the assignments in the SET provision. For instance, the accompanying explanation refreshes both last name and email segments of representative number 1056:

{`
UPDATE representatives
 SET
 lastname = 'Slope',
 email = 'mary.hill@classicmodelcars.com'
 WHERE
 employeeNumber = 1056;
 How about we check the changes:
 SELECT
 firstname, lastname, email
 FROM
 workers
 WHERE
 employeeNumber = 1056;
 `}

MySQL UPDATE numerous segments

Utilizing MySQL UPDATE to refresh lines returned by a SELECT articulation

You can supply the qualities for the SET proviso from a SELECT articulation that inquiries information from different tables.

For instance, in the clients table, a few clients don't have any deal agent. The estimation of the section saleRepEmployeeNumber is NULL as pursues:

{`
SELECT customername,
 salesRepEmployeeNumber
 FROM
 clients
 WHERE
 salesRepEmployeeNumber IS NULL;
 `}

We can take a deal delegate and update for those clients.

To do this, we can choose an arbitrary worker whose activity title is Sales Rep from the representatives table and update it for the representatives table.

This inquiry chooses an irregular worker from the representatives table whose activity title is the Sales Rep.

{`
SELECT
 employeeNumber
 FROM
 workers
 WHERE
 jobtitle = 'Deals Rep'
 Request BY RAND()
 `}

Point of confinement 1;

To refresh the business agent representative number section in the clients table, we place the question above in the SET proviso of the UPDATE articulation as pursues:

{`
UPDATE clients
 SET
 salesRepEmployeeNumber = (SELECT
 employeeNumber
 FROM
 workers
 WHERE
 jobtitle = 'Deals Rep'
 Breaking point 1)
 WHERE
 salesRepEmployeeNumber IS NULL;
 `}

On the off chance that you question information from the workers table, you will see that each client has a business delegate. At the end of the day, the accompanying inquiry restores no column.

{`
SELECT
 salesRepEmployeeNumber
 FROM
 clients
 WHERE
 salesRepEmployeeNumber IS NOT NULL;
 `}

In this instructional exercise, you have figured out how to utilize MySQL UPDATE explanation to refresh information in a database table.

MYSQL Tutorials

• Create MySQL Database
• Create Table in MySQL
• Insert data to MySQL
• Mysql Queries
• Update data in MYSQL
• Delete data in MySQL
• Default Database
• Primary Key
• Foreign Keys
• Views
• Export MySQL Database
• Import data into Mysql
• Create Website with MySQL

MYSQL Sample Assignments

• COIT20247 Database Design and Development
• Relational schema and MySQL database
• Mysql Project
• ITICT107A Introduction to Databases
• MySql Assignment Question

MYSQL Sample Solutions

• Solution - COIT20247 Database Design
• Solution - Relational schema and MySQL
• Solution - Mysql Project

Testimonials

Very affordable projects!! And that to submit before deadlines. Thanks for helping me in my database project and raising my grades. I have been able to secure good marks in my internal assessment only because of you. Read More