INSERT...SELECT query in mysql

Question

I have two tables:

`user1`
- full_name
- headline # this is empty

`user2`
- full_name
- headline # this has content

I want to insert the headline from user2 table into user1 table. This is what I have so far:

insert into user1 set headline = (select headline from user2 where headline=headline)

However, I get an error message from this saying the select returns more than one row. How would I correctly issue this insert statement?

eggyal · Accepted Answer · 2012-07-22 11:16:44Z

1

It's not entirely clear what you want to do. In particular, what happens when there are multiple records in each table? If you want to copy the headline from user2 into user1 where the full_name matches, you can use the multiple-table UPDATE syntax to join the tables and update user1:

UPDATE user1 JOIN user2 USING (full_name) SET user1.headline = user2.headline

answered Jul 22, 2012 at 11:16

eggyal

127k18 gold badges220 silver badges244 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Mike Perrenoud · Accepted Answer · 2012-07-22 11:21:15Z

0

If you truly want an INSERT then you want

INSERT INTO user1 SELECT full_name, headline

If you're actually trying to UPDATE then you want

UPDATE user1 SET headline = (SELECT headline FROM user2 WHERE user1.full_name = user2.full_name)

answered Jul 22, 2012 at 11:21

Mike Perrenoud

68.1k32 gold badges167 silver badges238 bronze badges

Collectives™ on Stack Overflow

INSERT...SELECT query in mysql

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related