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
– Strawberry, Commented Nov 30, 2015 at 18:18
you cannot update 1 row colA with up to 1000 other rows colWhatever, in your pseudo code sorta thing — Drew
– Drew, Commented Nov 30, 2015 at 18:24
@Drew As stated I have no corresponding columns to do a JOIN ON a.column_a = b.column_b — John Cliven
– John Cliven, Commented Nov 30, 2015 at 18:29

Drew · Accepted Answer · 2015-11-30 18:55:41Z

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        |
+--------+--------------+

Nick · Accepted Answer · 2015-11-30 18:31:43Z

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, 2015 at 18:31

Nick

10.2k8 gold badges65 silver badges97 bronze badges

3 Comments

John Cliven Over a year ago

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.

Nick Over a year ago

Can you explain your target with examples of data tables?

John Cliven Over a year ago

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.

Collectives™ on Stack Overflow

MYSQL: UPDATE rows based on SELECT from another table

2 Answers 2

Schema

Query (similar to yours)

Results

Comments

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Schema

Query (similar to yours)

Results

Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related