Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How to update multiple ids at once

Tags:

sql

mysql

I just began learning how to code w/ SQL. I am following a tutorial on Codeacedemy.com

Here is something I wrote for the fun of it, a simple date base:

CREATE TABLE employees (id INTEGER, name TEXT, year INTEGER);
INSERT INTO employees (id, name, year) VALUES (1, 'Dave', 2010);
INSERT INTO employees (id, name, year) VALUES (2, 'Karen', 2001);
INSERT INTO employees (id, name, year) VALUES (3, 'Joe', 2009);
INSERT INTO employees (id, name, year) VALUES (4, 'Larry', 2013);
INSERT INTO employees (id, name, year) VALUES (5, 'Tammy', 2015);
INSERT INTO employees (id, name, year) VALUES (6, 'Samantha', 2005);
INSERT INTO employees (id, name, year) VALUES (7, 'Karen', 2010);
INSERT INTO employees (id, name, year) VALUES (8, 'Rick', 2011);

ALTER TABLE employees ADD COLUMN gender TEXT;

UPDATE employees
set gender = 'Male'
where id = 1;

SELECT * FROM employees;

Is there a way that I can update multiple rows at once using their id? For example, I can use id 1, 3, 5, 8 and they'll all be updated to 'male'.

Thanks!

like image 807
Tanner Temoshenko Avatar asked Nov 22 '16 23:11

Tanner Temoshenko


People also ask

How do you update multiple records in SQL?

UPDATE config SET t1. config_value = 'value' , t2. config_value = 'value2' WHERE t1. config_name = 'name1' AND t2.

How do you update multiple values in one column in SQL?

First, specify the table name that you want to change data in the UPDATE clause. Second, assign a new value for the column that you want to update. In case you want to update data in multiple columns, each column = value pair is separated by a comma (,). Third, specify which rows you want to update in the WHERE clause.

How do I update multiple values in one column in MySQL?

MySQL UPDATE multiple columnsMySQL UPDATE command can be used to update multiple columns by specifying a comma separated list of column_name = new_value. Where column_name is the name of the column to be updated and new_value is the new value with which the column will be updated.


2 Answers

This for using where clause :

 UPDATE employees SET gender = 'Male' WHERE id IN (1,2,3)

If you want update all rows in table then:

UPDATE employees SET gender = 'Male'  
like image 65
Mansoor Avatar answered Sep 19 '22 15:09

Mansoor


You can use the same thing, but for the ID's you can use ID's in

Like this:

 ....
 where id in (1,2,3);

You can also write a nested query inside IN.

For more details on how to write IN you can refer here.

like image 32
Pritam Banerjee Avatar answered Sep 20 '22 15:09

Pritam Banerjee