Updating multiple records at the same time

Navigation
4

I have a list with the id and email of each user 

id  email
1   [email protected]  
2   [email protected]  
3   joao@gmailcom  
...

How do I set up an sql statement to update all emails at once in my users table?

My table: 

usuarios
  id          
  nome        
  sobrenome   
  telefone    
  email

I know that to update a record at a time would look like this: 

UPDATE usuarios SET email = '[email protected]' WHERE id = 1

But how to do with several at once?

I tried to do something like this but it did not work: 

UPDATE usuarios 

SET email =  
'[email protected]',
'[email protected]',
'joao@gmailcom'

WHERE id = 
1,
2,
3
    
asked by anonymous 08.03.2018 / 15:13

2 answers

6

Check if this helps you: 

UPDATE usuarios
    SET email = CASE id
        WHEN 1 THEN '[email protected]' 
        WHEN 2 THEN '[email protected]'
        WHEN 3 THEN '[email protected]'
        ELSE id
    END
;

If necessary, you can remove the ELSE clause and add the WHERE clause explicitly: 

WHERE id IN (1, 2, 3);
    
08.03.2018 / 15:32
6

I created in SQLFiddle an example based on what you passed us in the structure of your table, and I adequei with the answer @escapistabr so you can check if it meets your need.

  

Create Table 

CREATE TABLE IF NOT EXISTS 'usuarios' (
  'id' int(6) unsigned NOT NULL,
  'nome' varchar(256) NOT NULL,
  'sobrenome' varchar(200) NOT NULL,
  'telefone' int(8) unsigned NOT NULL,
  'email' varchar(256) NOT NULL, 
  PRIMARY KEY ('id')
) DEFAULT CHARSET=utf8;
INSERT INTO 'usuarios' ('id', 'nome', 'sobrenome', 'telefone', 'email') VALUES
  ('1', 'ana', 'fulana', '12345678', ''),
  ('2', 'maria', 'beltrana', '87654321', ''),
  ('3', 'joao', 'ciclano', '14785236', '');
  

Update 

SET email = CASE id
             WHEN 1 THEN '[email protected]' 
             WHEN 2 THEN '[email protected]'
             WHEN 3 THEN '[email protected]'
            END
WHERE id in (1, 2, 3);

I added a comment on SQLFiddle in case you wanted to perform the update test you remove the update comment because the same in sqlfiddle only executes in Schema.

Take your test: SQLFiddle

    
08.03.2018 / 16:16
What is Java's sort? Operator in in java