CODEWITHSUNDEEP
mysqlsql
Ortixx
Ortixx

Reputation: 833

Copy values from one table to another in MySQL

I have 2 tables users and partners. They both contain a created_on field but that field is new and thus empty in the partners table. The partners table also has a user_id and partner_id field. The user_id field is the FK to the users table.

I need to copy the values of the column created_on from the users tables into the partners table for each user WHERE partners.user_id = users.id.

I want to do that in SQL (my RDBMS is MySQL) so no PHP (if possible)

(Some pseudo code to get my problem across better)

Copying data is not good practice, I know that, but I need this for a specific case

Upvotes: 0

Views: 59

Answers (2)

Miniver Cheevy
Miniver Cheevy

Reputation: 1677

How about something like this

CREATE TABLE USERS
(
user_id int,
  created_on datetime
);
CREATE TABLE PARTNERS
(
partner_id int,
user_id int,
created_on datetime
);
insert into users (user_id, created_on) values (1,"2000-1-1");
insert into users (user_id, created_on) values (2, "2001-1-1");
insert into partners (partner_id, user_id, created_on) values (10,1,null);
insert into partners (partner_id, user_id, created_on) values (11,2,null);


Update Partners p
INNER JOIN users u
on p.user_id = u.user_id
Set p.created_on = u.created_on;

select * from partners

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1271121

You can do this using update/join:

update partners p join
       users u
       on p.user_id = u.id
    set p.created_on = u.created_on;

Upvotes: 1

Related Questions