CODEWITHSUNDEEP
mysqlsqlsql-insert
DMach
DMach

Reputation: 87

Inserting new data in a table

I have created a basic table for learning purposes.

CREATE TABLE friends (
  id INT,
  name TEXT,
  birthday DATE
);

Added some data...

INSERT INTO friends (id,name,birthday)
VALUES (1,'Jo Monro','1948-05-30');

INSERT INTO friends (id,name,birthday)
VALUES (2, 'Lara Johnson','1970-03-03');

INSERT INTO friends (id,name,birthday)
VALUES (3,'Bob Parker', '1962-09-3');

And I realised that I forgot to include the email column. I added the column...

ALTER TABLE friends
ADD COLUMN email;

..but how can I add now data to this new column only?

I have tried WHERE statements, rewriting the INSERT INTO statements with and without the other column names but nothing worked?

What am I missing here?

Thank you!

Upvotes: 0

Views: 56

Answers (1)

LukStorms
LukStorms

Reputation: 29677

Insert the emails into a temporary table, then update the real table with that.

CREATE TABLE friends (
  id INT auto_increment primary key,
  name VARCHAR(100),
  birthday DATE
);

INSERT INTO friends (name, birthday) VALUES 
  ('Jo Monro','1948-05-30')
, ('Lara Johnson','1970-03-03')
, ('Bob Parker', '1962-09-3');

ALTER TABLE friends ADD COLUMN email VARCHAR(100);

select * from friends

id | name         | birthday   | email
-: | :----------- | :--------- | :----
 1 | Jo Monro     | 1948-05-30 | null 
 2 | Lara Johnson | 1970-03-03 | null 
 3 | Bob Parker   | 1962-09-03 | null 

--
-- temporary table for the emails
--
CREATE TEMPORARY TABLE tmpEmails (
 name varchar(100) primary key,
 email varchar(100)
);

--
-- fill the temp
--
insert into tmpEmails (name, email) values
  ('Jo Monro','[email protected]')
, ('Lara Johnson','[email protected]')
, ('Bob Parker', '[email protected]');

--
-- update the real table
--
update friends friend
join tmpEmails tmp
  on friend.name = tmp.name
set friend.email = tmp.email
where friend.email is null;

select * from friends

id | name         | birthday   | email                           
-: | :----------- | :--------- | :-------------------------------
 1 | Jo Monro     | 1948-05-30 | [email protected]             
 2 | Lara Johnson | 1970-03-03 | [email protected]         
 3 | Bob Parker   | 1962-09-03 | [email protected]

db<>fiddle here

Upvotes: 2

Related Questions