How-To copy column values from a table to another in MySQL

Imagine table A and B like:

SELECT * FROM A WHERE (...) LIMIT 3
id, a, x, c
__________
2, 5, 1, 6
7, 2, 6, 1
9, 8, 2, 2

SELECT * FROM B WHERE (...) LIMIT 3
id, x, b, c
__________
1, NULL, 3, 3
5, NULL, 0, 4
3, NULL, 2, 7

You want to copy x column values from A (i.e. 1,6,2) to B (all NULL).

Solution:

SELECT @i:=0;
SELECT @j:=0;
UPDATE B
JOIN (
SELECT @i:=@i+1 AS tid, B.id FROM B WHERE (...) LIMIT 3
) Bt ON (Bt.id=B.id)
JOIN (
SELECT @j:=@j+1 AS tid, A.id from A WHERE (...) LIMIT 3
) At ON (At.tid=Bt.tid)
SET B.x = At.x;