How to run multiple update statements in mysql

In SQL, sometimes we need to update multiple records in a single query. We will use the UPDATE keyword to achieve this. For this, we use 2 kinds of examples i.e. the first based on only one condition and the second based on multiple conditions. For this article, we will be using the Microsoft SQL Server as our database and Select keyword.

Step 1: Create a Database. For this use the below command to create a database named GeeksForGeeks.

Query:

CREATE DATABASE GeeksForGeeks

Output:

Step 2: Use the GeeksForGeeks database. For this use the below command.

Query:

USE GeeksForGeeks

Output:

Step 3: Create a table of BANDS inside the database GeeksForGeeks. This table has 3 columns namely BAND_NAME, PERFORMING_COST, and NUMBER_OF_MEMBERS containing the names, charges for performing, and the number of members of a band.

Query:

CREATE TABLE BANDS(
BAND_NAME VARCHAR(20),
PERFORMING_COST INT,
NUMBER_OF_MEMBERS INT);

Output:

Step 4: Describe the structure of the table BANDS.

Query:

EXEC SP_COLUMNS BANDS;

Output:

Step 5: Insert 5 rows into the BANDS table.

Query:

INSERT INTO BANDS VALUES('INDIAN OCEAN',10000,5);
INSERT INTO BANDS VALUES('BTS',20000,6);
INSERT INTO BANDS VALUES('METALLICA',30000,10);
INSERT INTO BANDS VALUES('BEATLES',40000,4);
INSERT INTO BANDS VALUES('EAGLES',50000,4);

Output:

Step 6: Display all the rows of the BANDS table.

Query:

SELECT * FROM BANDS;

Output:

Step 7: Update all records of the table BANDS satisfying only a single condition. The condition here is that the value of the column NUMBER_OF_MEMBERS should be less than 5. If the condition is satisfied then the value of the column PERFORMING_COST doubles itself. Use the keyword UPDATE and WHERE to achieve this.

Syntax:

UPDATE TABLE_NAME SET COLUMN_NAME
= VALUE WHERE CONDITION;

Query:

UPDATE BANDS SET PERFORMING_COST = 
2*PERFORMING_COST WHERE NUMBER_OF_MEMBERS<=5;

Output:

Step 8: Display all the rows of the updated FIRM table.

Query:

SELECT * FROM BANDS;

Note: The PERFORMING_COST has been doubled for the bands having NUMBER_OF_MEMBERS<=5.

Output:

Step 9: Update all records of the table BANDS satisfying two(multiple) conditions. The condition here is if the BAND_NAME is ‘METALLICA’, then its PERFORMING_COST is set to 90000 and if the BAND_NAME is ‘BTS’, then its PERFORMING_COST is set to 200000. Use the keyword UPDATE and WHEN to achieve this. This query behaves like an if-else if-else block.

Syntax:

UPDATE TABLE_NAME
SET COLUMN_VALUE 
= CASE COLUMN_NAME
WHEN 'COLUMN_NAME1' THEN COLUMN_VALUE1
WHEN 'COLUMN_NAME2' THEN COLUMN_VALUE2
ELSE COLUMN_VALUE
END
WHERE BAND_NAME IN('COLUMN_NAME1', 'COLUMN_NAME2');

Query:

UPDATE BANDS
SET PERFORMING_COST 
= CASE BAND_NAME
WHEN 'METALLICA' THEN 90000
WHEN 'BTS' THEN 200000
ELSE PERFORMING_COST
END
WHERE BAND_NAME IN('METALLICA', 'BTS');

Output:

Step 10: Display all the rows of the updated FIRM table.

Query:

SELECT * FROM BANDS;

Note: The PERFORMING_COST for METALLICA and BTS have been updated to 90000 and 200000 respectively.

Output:

How do I write multiple update statements in MySQL?

How do you update multiple statements in SQL?

Can you do multiple sets in SQL update?

What are the 3 update commands in SQL?