Sql modifying data - MYSQL part I

SQL Ahmed Farag Information Systems Dept. Faculty of Computers and Information Menoufia University, Egypt.

MySQL Insert • The INSERT statement allows you to insert one or more rows into a table. The following illustrates the syntax of the INSERT statement: INSERT INTO table(c1,c2,...) VALUES (v1,v2,...); • In this syntax, • First, you specify the table name and a list of comma- separated columns inside parentheses after the INSERT INTO clause. • Then, you put a comma-separated list of values of the corresponding columns inside the parentheses following the VALUES keyword. • The number of columns and values must be the same. In addition, the positions of columns must be corresponding with the positions of their values.

MySQL Insert • To add multiple rows into a table using a single INSERT statement, you use the following syntax: • In this syntax, • First, you specify the table name and a list of comma- separated columns inside parentheses after the INSERT INTO clause. • Then, you put a comma-separated list of values of the corresponding columns inside the parentheses following the VALUES keyword. INSERT INTO table(c1,c2,...) VALUES (v11,v12,...), (v21,v22,...), ... (vnn,vn2,...);

MySQL Insert examples • Example 1: INSERT INTO productlines VALUES ('Mobiles','Mobiles',null,null);

MySQL Insert examples • Example 2: INSERT INTO productlines (productLine,textDescription) VALUES ('TV','TV');

MySQL INSERT INTO SELECT • Besides using row values in the VALUES clause, you can use the result of a SELECT statement as the data source for the INSERT statement. • The following illustrates the syntax of the INSERT INTO SELECT statement: • As you can see, instead of using the VALUES clause, you can use a SELECT statement. The SELECT statement can retrieve data from one or more tables. INSERT INTO table_name(column_list) SELECT select_list FROM another_table;

MySQL INSERT INTO SELECT Example • Suppose we have the following suppliers table with the following structure: • Note that you will learn how to create a new table in the future tutorial. For now, you just need to execute this statement to create the suppliers table. CREATE TABLE suppliers ( supplierNumber INT AUTO_INCREMENT, supplierName VARCHAR(50) NOT NULL, phone VARCHAR(50), addressLine1 VARCHAR(50), addressLine2 VARCHAR(50), city VARCHAR(50), state VARCHAR(50), postalCode VARCHAR(50), country VARCHAR(50), customerNumber INT PRIMARY KEY (supplierNumber) );

MySQL INSERT INTO SELECT Example • Because of the new contracts, all customers from California, USA become the company’s suppliers. The following query finds all customers in California, USA: SELECT customerNumber, customerName, phone, addressLine1, addressLine2, city, state, postalCode, country FROM customers WHERE country = 'USA' AND state = 'CA';

MySQL INSERT INTO SELECT Example • Now, you need to insert these customers from the customers table into the suppliers table. The following INSERT INTO SELECT statement helps you to do so: INSERT INTO suppliers ( supplierName, phone, addressLine1, addressLin e2, city, state, postalCode, country, customerNumbe r ) SELECT customerName, phone, addressLine1, addressLine 2, city, state , postalCode, country, customerNumb er FROM customers WHERE country = 'USA' AND state = 'CA';

MySQL INSERT ON DUPLICATE KEY UPDATE

MySQL INSERT ON DUPLICA TE KEY UPDATE • When you insert a new row into a table if the row causes a duplicate in UNIQUE index or PRIMARY KEY , MySQL will issue an error. • However, if you specify the ON DUPLICATE KEY UPDATE option in the INSERT statement, MySQL will update the existing row with the new values instead. • The syntax of INSERT ON DUPLICATE KEY UPDATE statement is as follows: • The only addition to the INSERT statement is the ON DUPLICATE KEY UPDATE clause where you specify a list of column-value-pair assignments in case of duplicate. INSERT INTO table (column_list) VALUES (value_list) ON DUPLICATE KEY UPDATE c1 = v1, c2 = v2, ...;

MySQL INSERT ON DUPLICA TE KEY UPDATE • Basically, the statement first tries to insert a new row into the table. If a duplicate error occurs, it will update the existing row with the value specified in the ON DUPLICATE KEY UPDATE clause. • MySQL returns the number of affected-rows based on the action it performs: • If the new row is inserted, the number of affected-rows is 1. • If the existing row is updated, the number of affected- rows is 2. • If the existing row is updated using its current values, the number of affected-rows is 0.

MySQL INSERT ON DUPLICA TE KEY UPDATE • To use the values from the INSERT clause in the DUPLICATE KEY UPDATE clause, you use the VALUES() function as follows: INSERT INTO table_name(c1) VALUES(c1) ON DUPLICATE KEY UPDATE c1 = VALUES(c1) + 1; • The statement above sets the value of the c1 to its current value specified by the expression VALUES(c1) plus 1 if there is a duplicate in UNIQUE index or PRIMARY KEY.

MySQL INSERT ON DUPLICAT E KEY UPDATE Example • First, create a table named devices to store the network devices. • Next, insert rows into the devices table. CREATE TABLE devices ( id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(100) ); INSERT INTO devices(name) VALUES('Router F1'),('Switch 1'),('Switch 2'); • Then, query the data from the devices table to verify the insert: SELECT id, name FROM devices;

MySQL INSERT ON DUPLICAT E KEY UPDATE Example • After that, insert one more row into the devices table. INSERT INTO devices(name) VALUES ('Printer') ON DUPLICATE KEY UPDATE name = 'Printer'; • Because there is no duplicate, MySQL inserts a new row into the devices table. The statement above has the same effect as the following statement: INSERT INTO devices(name) VALUES ('Printer');

MySQL INSERT ON DUPLICAT E KEY UPDATE Example • Finally, insert a row with a duplicate value in the id column. • Because a row with id 4 already exists in the devices table, the statement updates the name from Printer to Central Printer. INSERT INTO devices(id,name) VALUES (4,'Printer') ON DUPLICATE KEY UPDATE name = 'Central Printer';

MySQL INSERT IGNORE Statement • When you use the INSERT statement to add multiple rows to a table and if an error occurs during the processing, MySQL terminates the statement and returns an error. As the result, no rows are inserted into the table. • However, if you use the INSERT IGNORE statement, the rows with invalid data that cause the error are ignored and the rows with valid data are inserted into the table. • The syntax of the INSERT IGNORE statement is as follows: INSERT IGNORE INTO table(column_list) VALUES( value_list), ( value_list), ...

MySQL INSERT IGNORE Statement • We will create a new table called subscribers for the demonstration. CREATE TABLE subscribers ( id INT PRIMARY KEY AUTO_INCREMENT, email VARCHAR(50) NOT NULL UNIQUE ); • The UNIQUE constraint ensures that no duplicate email exists in the email column. • The following statement inserts a new row into the subscribers table: INSERT INTO subscribers(email) VALUES('john.doe@gmail.com'); • It worked as expected.

MySQL INSERT IGNORE Statement • Let’s execute another statement that inserts two rows into the subscribers table: • It returns an error. • As indicated in the error message, the email john.doe@gmail.com violates the UNIQUE constraint. • However, if you use the INSERT IGNORE statement instead. INSERT INTO subscribers(email) VALUES('john.doe@gmail.com'), ('jane.smith@ibm.com'); Error Code: 1062. Duplicate entry 'john.doe@gmail.com' for key 'e mail' INSERT IGNORE INTO subscribers(email) VALUES('john.doe@gmail.com'), ('jane.smith@ibm.com'); • MySQL returned a message indicating that one row was inserted and the other row was ignored. 1 row(s) affected, 1 warning(s): 1062 Duplicate entry 'john.doe@gmail.com' for key 'email' Records: 2 Duplicates: 1 Warnings: 1

MySQL INSERT IGNORE Statement • To find the detail of the warning, you can use the SHOW WARNINGS command as shown below: SHOW WARNINGS; • If you query data from subscribers table, you will find that only one row was actually inserted and the row that causes the error was not.

MySQL INSERT IGNORE Statement • You use the UPDATE statement to update existing data in a table. you can also use the UPDATE statement to change column values of a single row, a group of rows, or all rows in a table. • The following illustrates the syntax of the MySQL UPDATE statement: UPDATE [LOW_PRIORITY] [IGNORE] table_name SET column_name1 = expr1, column_name2 = expr2, ... [WHERE condition];

MySQL INSERT IGNORE Statement • In the UPDATE statement: • First, specify the table name that you want to update data after the UPDATE keyword. • Second, the SET clause specifies which column that you want to modify and the new values. To update multiple columns, you use a list of comma-separated assignments. You supply a value in each column’s assignment in the form of a literal value, an expression, or a subquery. • Third, specify which rows to be updated using a condition in the WHERE clause. The WHERE clause is optional. If you omit the WHERE clause, the UPDATE statement will update all rows in the table. • Notice that the WHERE clause is so important that you should not forget. Sometimes, you may want to change just one row; However, you may forget the WHERE clause and accidentally update all rows of the table.

MySQL INSERT IGNORE Statement • MySQL supports two modifiers in the UPDATE statement. • The LOW_PRIORITY modifier instructs the UPDATE statement to delay the update until there is no connection reading data from the table. The LOW_PRIORITY takes effect for the storage engines that use table-level locking only, for example, MyISAM, MERGE, MEMORY. • The IGNORE modifier enables the UPDATE statement to continue updating rows even if errors occurred. The rows that cause errors such as duplicate-key conflicts are not updated.

Using MySQL UPDATE to modify values in a single column example • In this example, we are going to update the email of Mary Patterson to the new email mary.patterso@classicmodelcars.com. • First, to ensure the update is successful, we query Mary’s email from the employees table using the following SELECT statement: SELECT firstname, lastname, email FROM employees WHERE employeeNumber = 1056;

Using MySQL UPDATE to modify values in a single column example • Second, we can update Mary’s email to the new email mary.patterson@classicmodelcars.com using the UPDATE statement as shown in the following query: UPDATE employees SET email = 'mary.patterson@classicmodelcars.com' WHERE employeeNumber = 1056; • Because we just want to update one row, we use the WHERE clause to specify the row using the employee number 1056. The SET clause sets the value of the email column to the new email. • Third, we execute the SELECT statement again to verify the change. SELECT firstname, lastname, email FROM employees WHERE employeeNumber = 1056;

Using MySQL UPDATE to modify values in multiple columns • To update values in the multiple columns, you need to specify the assignments in the SET clause. For example, the following statement updates both last name and email columns of employee number 1056: UPDATE employees SET lastname = 'Hill', email = 'mary.hill@classicmodelcars.com' WHERE employeeNumber = 1056; • To update values in the multiple columns, you need to specify the assignments in the SET clause. For example, the following statement updates both last name and email columns of employee number 1056:

Using MySQL UPDATE to update rows returned by a SELECT statement

MySQL INSERT IGNORE Statement • You can supply the values for the SET clause from a SELECT statement that queries data from other tables. • For example, in the customers table, some customers do not have any sale representative. The value of the column saleRepEmployeeNumber is NULL as follows: SELECT customername, salesRepEmployeeNumber FROM customers WHERE salesRepEmployeeNumber IS NULL;

MySQL INSERT IGNORE Statement UPDATE customers SET salesRepEmployeeNumber = (SELECT employeeNumber FROM employees WHERE jobtitle = 'Sales Rep' LIMIT 1) WHERE salesRepEmployeeNumber IS NULL;

Sql modifying data - MYSQL part I

More Related Content

What's hot

Similar to Sql modifying data - MYSQL part I

More from Ahmed Farag

Recently uploaded

Sql modifying data - MYSQL part I