Multiple updates in one line? Objective 6


#1


https://www.codecademy.com/en/courses/learn-sql/projects/learn_sql_create_table


Is there a way to use one line to input all three/four of your friends emails? Or do you have to do individual lines for each friend? I managed to figure out how to update everyones birthdays in one line, but couldn't get it do update everyones email.

I feel like there must be a much more sensible efficient way to do this. I've posted below the code that allowed multiple birthday inputs. I tried several variations on emails but nothing worked.....
(Also yes I know those aren't their real birthdays but I ain't spending time digging for the actual ones :stuck_out_tongue: I got the years right.)



The following code did work:
INSERT INTO friends (id, name, birthday) VALUES (1, 'Jane Doe', '19.05.1993');

INSERT INTO friends (id, name, birthday) VALUES (2, 'Roland Deschain', '19.09.1099'), (3, 'Eddie Dean', '08.02.1964'), (4, 'Susannah Dean', '05.03.1937');

The below hasn't worked:
UPDATE friends SET email = 'jdoe@example.com' WHERE id = 1, email = 'last@gunslinger.net' WHERE id = 2, email = 'eddie@gunslinger.net' WHERE id = 3, email = 'susannah@gunslinger.net' WHERE id = 4;

I have tried enclosing everything in brackets etc, but no joy.

I know it's not specifically what the thing wants, but I feel there's got to be an efficient method.


INSERT INTO friends (id, name, birthday) VALUES (1, 'Jane Doe', '19.05.1993');

INSERT INTO friends (id, name, birthday) VALUES (2, 'Roland Deschain', '19.09.1099'), (3, 'Eddie Dean', '08.02.1964'), (4, 'Susannah Dean', '05.03.1937');

UPDATE friends SET email = 'jdoe@example.com' WHERE id = 1, email = 'last@gunslinger.net' WHERE id = 2, email = 'eddie@gunslinger.net' WHERE id = 3, email = 'susannah@gunslinger.net' WHERE id = 4;


#2

This works:

UPDATE friends
SET email = CASE id
WHEN 1 Then 'jdoe@example.com'
WHEN 2 Then '2@example.com'
WHEN 3 Then '3@example.com'
WHEN 4 Then '4@example.com'
ELSE 'none'
END

WHERE id IN (1,2,3,4);

Another way is using multiple JOINS for each row - seems redundant to me..