CODEWITHSUNDEEP
phpmysqlsql
John Cliven
John Cliven

Reputation: 1231

MYSQL: UPDATE rows based on SELECT from another table

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.

Upvotes: 1

Views: 2243

Answers (2)

Drew
Drew

Reputation: 24949

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

Upvotes: 1

Nick
Nick

Reputation: 10143

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

Upvotes: 0

Related Questions