Updating multiple records at the same time

Asked

Viewed 77 times

3

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 mount an sql statement to update all emails at once in my user table?

My table:

usuarios
  id          
  nome        
  sobrenome   
  telefone    
  email

I know that to update one 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 didn’t work:

UPDATE usuarios 

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

WHERE id = 
1,
2,
3
mysql sql

2 answers

5


Make sure it helps you this way:

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 clause ELSE and add the clause WHERE explicitly:

WHERE id IN (1, 2, 3);

5

I created in the SQLFiddle an example based on what you gave us of the structure of your table, and I adjusted with the response of @escapistabr so that 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 runs in Schema.

Take your test: Sqlfiddle

Browser other questions tagged mysql sql

You are not signed in. Login or sign up in order to post.