更新MYSQL中另一个表的空字段
我正在寻找一些MYSQL帮助来更新另一个表中的空字段。更新MYSQL中另一个表的空字段
我想从Spare_Mobile_Numbers表中分配一个数字给user_table.MobileNumber中有空白的任何用户。
我有用户数据的一个表:
User_table (table)
----------------------------------------------
Name Email MobileNumber
Rob [email protected] <blank>
Jane [email protected] 07700000001
Penny [email protected] 07700000002
John [email protected] <blank>
Gavin [email protected] 07700000003
Spare_Mobile_Numbers (table)
----------------------------------------------
07700000004
07700000005
07700000006
07700000007
我想从Spare_Mobile_Numbers表将数字分配给任何用户与所述user_table.MobileNumber空白。
您必须在交易中执行此操作,因为在分配了备用号后,您需要将其从可用列表中删除。
请确保您使用的是像InnoDB这样的事务引擎。
,做:
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;
我在这里做了几个假设:
-
user_table.id
是主键,如果没有使用真正的PK,而不是,或使用user_table.email
作为连接条件。 “u1 ON (u.email = u1.email)
” - sparenumber是在
spare_mobile_numbers
中列出的字段的名称。
请注意,MySQL不允许您更新表,同时在子查询中从该表中进行选择。勉强它允许你在一个子子程序中使用同一张表,这正是我在这里所做的。
小问题Johan'@urank:= 1 WHERE u3.MobileNumber IS NULL)u1 ON(u.id = u1.id) 我在4号线 – user965526
@ user965526,谢谢修正它。 – Johan
aagghh - 我真的很抱歉约翰,我一直在努力工作这个代码,但无济于事。我有一个新的错误。 #1054 - 'on子句'中的未知列'u.rank' – user965526
然后,spare_mobile_numbers中的条目是不准确的,因为其中有些条目不再是闲置的......是一个问题吗? –
'Spare_Mobile_Numbers'中有'user_name'或'user_id'列吗? – bfavaretto
在单个查询中无法真正做到这一点,因为您不能在分配数字时更新备用表,因此您需要某种循环来分配备用数字,然后将其标记为不可用。 –