MYSQL: UPDATE rows based on SELECT from another table

Question

I have a 'users' table with 100 entries, each having an empty 'first_name' column. I wish to update each of these with names from another table. They do not need to correspond, they can be random, I just need data from one table into the other. I have found other people asking similar questions, but they all seem to have corresponding columns, like "username" being the same in either table and can get it working using a JOIN ON. As there are no corresponding columns I cannot do this.

I currently have tried the following which does not work:

UPDATE users
SET first_name =
  (
   SELECT `user_firstname`
   FROM past_users
   WHERE `active` = '1' LIMIT 100
  )

This gives the error:

Subquery returns more than 1 row

The only way it works is using LIMIT 1, which updates each entry with the same data. I want them each to be unique.

Note that LIMIT without ORDER BY is a fairly meaningless concept. But once you figure that out, see JOIN. — Strawberry, Nov 30 '15 at 18:18
you cannot update 1 row colA with up to 1000 other rows colWhatever, in your pseudo code sorta thing — Drew, Nov 30 '15 at 18:24
Possible duplicate of [How to use JOIN in UPDATE query?](http://stackoverflow.com/questions/15209414/how-to-use-join-in-update-query) — Drew, Nov 30 '15 at 18:25
@Drew As stated I have no corresponding columns to do a `JOIN ON a.column_a = b.column_b` — John Cliven, Nov 30 '15 at 18:29

Drew · Accepted Answer · 2015-11-30T18:55:41.183

Ok, maybe this concept. The below is just an illustration. Uses random, and limit 1.

Schema

create table user
(   userId int auto_increment primary key,
    firstName varchar(50) not null
    -- etc
);

create table prevUser
(   userId int auto_increment primary key,
    firstName varchar(50) not null,
    active int not null
);

-- truncate table user;
-- truncate table prevuser;
insert user(firstName) values (''),(''),(''),(''),(''),(''),(''),(''),('');

insert prevUser(firstName,active) values 
('user1prev',0),('snickers bar',1),('Stanley',1),('user4prev',0),('zinc',1),
('pluto',1),('us7545rev',0),('uffallfev',0),('user4prev',0),('tuna',1),
('Monty Python',1),('us4 tprev',0),('mouse',1),('user4prev',0),('Sir Robin',1),
('lizard',1),('Knights that says, Nee!',0),('mayo',1),('656user4prev',0),('kiwi',1);

Query (similar to yours)

UPDATE user
SET firstName =
  (
   SELECT firstName
   FROM prevUser
   WHERE `active` = '1'
   order by rand()
   limit 1
  )

Results

select * from user;
+--------+--------------+
| userId | firstName    |
+--------+--------------+
|      1 | snickers bar |
|      2 | tuna         |
|      3 | mouse        |
|      4 | Sir Robin    |
|      5 | mouse        |
|      6 | mayo         |
|      7 | lizard       |
|      8 | snickers bar |
|      9 | pluto        |
+--------+--------------+

score 0 · Answer 2 · answered Nov 30 '15 at 18:31

0

You need something like this:

UPDATE users 
JOIN past_users ON past_users.user_id = users.id AND past_users.`active` = '1'
SET users.first_name = past_users.user_firstname

answered Nov 30 '15 at 18:31

Nick

9,735
7
59
89

Thanks mnv, however `ON past_users.user_id = users.id` will not be true as these columns do not correspond. Nothing corresponds between the two tables, I merely want to select from one unrelated table into another. – John Cliven Nov 30 '15 at 18:35
Can you explain your target with examples of data tables? – Nick Nov 30 '15 at 18:36
`past_users` has 2 columns: `active` (`1/0`) signifying whether it should be used or not, and `user_firstname` which is just an alpha, varchar, first name. I want to select 100 of these names and put them into a different table called `users`. – John Cliven Nov 30 '15 at 18:41

MYSQL: UPDATE rows based on SELECT from another table

2 Answers2

Schema

Query (similar to yours)

Results