CODEWITHSUNDEEP
mysqlsql
David542
David542

Reputation: 110163

INSERT...SELECT query in mysql

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?

Upvotes: 0

Views: 168

Answers (2)

Mike Perrenoud
Mike Perrenoud

Reputation: 67898

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)

Upvotes: 0

eggyal
eggyal

Reputation: 125865

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

Upvotes: 1

Related Questions