Update an empty field from another table in MYSQL [closed]

Closed. This question needs details or clarity. It is not currently accepting answers.

---

Want to improve this question? Add details and clarify the problem by editing this post.

Closed 5 years ago.

Improve this question

I am looking for some MYSQL help on updating an empty field from another table.

I would like to allocate a number from the Spare_Mobile_Numbers table to any user with a blank in the user_table.MobileNumber.

I have a table of user data:

U开发者_JS百科ser_table (table)
----------------------------------------------
Name    Email               MobileNumber
Rob     rob@email.com       <blank>
Jane    jane@email.com      07700000001
Penny   Jenny@email.com     07700000002
John    John@email.com      <blank>
Gavin   Gavin@email.com     07700000003

Spare_Mobile_Numbers (table)
----------------------------------------------
07700000004
07700000005
07700000006
07700000007

I would like to allocate a number from the Spare_Mobile_Numbers table to any user with a blank in the user_table.MobileNumber.

---

You will have to do this in a transaction, because after the spare number is assign you need to remove it from the available list.

Make sure you use a transactional engine like InnoDB.

And do:

START TRANSACTION;
  UPDATE user_table u
  INNER JOIN (SELECT * FROM (SELECT id, @urank:= @urank + 1 as rank 
              FROM user_table u3
              CROSS JOIN (select @urank:= 1) q
              WHERE u3.MobileNumber IS NULL) u2) u1 ON (u.id = u1.id)
  INNER JOIN (SELECT @smrank:= @smrank +1 as rank, sparenumber 
              FROM spare_mobile_numbers
              CROSS JOIN (select @smrank:= 1) q2) sm ON (u1.rank = sm.rank)
  SET u.MobileNumber = sm.sparenumber;

  DELETE sm FROM spare_mobile_numbers sm 
  INNER JOIN user_table u ON (sm.sparenumber = u.MobileNumber);
COMMIT;

I've made a few assumptions here:

1. user_table.id is the primary key, if it is not use the real PK instead or use user_table.email as the join condition. "u1 ON (u.email = u1.email)"
2. sparenumber is the name of the field listed in spare_mobile_numbers.

Note that MySQL does not allow you to update a table, whilst selecting from that table in a subselect at the same time. Strangly it does allow you to use that same table in a sub-subselect, which is what I'm doing here.