Return to Answer

##Answer to question 1

This should do it for you

UPDATE user_activity a
INNER JOIN user_profile b 
USING (user_profile_id)
SET a.user_profile_id_int = b.user_profile_id_int;

This will work in MySQL. If you are not used to that JOIN syntax, do this:

UPDATE user_activity a
INNER JOIN user_profile b 
ON a.user_profile_id = b.user_profile_id
SET a.user_profile_id_int = b.user_profile_id_int;

Both should work.

##Answer to question 2

Your query, in theory, works. However, look at what it is doing:

A table scan on user_activity, an indexed lookup of the user_profile_id_int using the PRIMARY KEY of user_profile, and an in-place update of the current row in user_activity.

The query is hitting two tables and two primary keys, back and forth, on a per-row basis. All steps slow each other down. Thus, you get a longer running query.

##CAVEAT

Adding a compound index on user_profile should speed things up:

ALTER TABLE user_profile ADD INDEX (user_profile_id,user_profile_id_int);

Answer to question 1

This should do it for you

UPDATE user_activity a
INNER JOIN user_profile b 
USING (user_profile_id)
SET a.user_profile_id_int = b.user_profile_id_int;

This will work in MySQL. If you are not used to that JOIN syntax, do this:

UPDATE user_activity a
INNER JOIN user_profile b 
ON a.user_profile_id = b.user_profile_id
SET a.user_profile_id_int = b.user_profile_id_int;

Both should work.

Answer to question 2

Your query, in theory, works. However, look at what it is doing:

A table scan on user_activity, an indexed lookup of the user_profile_id_int using the PRIMARY KEY of user_profile, and an in-place update of the current row in user_activity.

The query is hitting two tables and two primary keys, back and forth, on a per-row basis. All steps slow each other down. Thus, you get a longer running query.

CAVEAT

Adding a compound index on user_profile should speed things up:

ALTER TABLE user_profile ADD INDEX (user_profile_id,user_profile_id_int);

##Answer to question 1

This should do it for you

UPDATE user_activity a
INNER JOIN user_profile b 
USING (user_profile_id)
SET a.user_profile_id_int = b.user_profile_id_int;

This will work in MySQL. If you are not used to that JOIN syntax, do this:

UPDATE user_activity a
INNER JOIN user_profile b 
ON a.user_profile_id = b.user_profile_id
SET a.user_profile_id_int = b.user_profile_id_int;

Both should work.

##Answer to question 2

Your query, in theory, works. However, look at what it is doing:

A table scan on user_activity, an indexed lookup of the user_profile_id_int using the PRIMARY KEY of user_profile, and an in-place update of the current row in user_activity.

The query is hitting two tables and two primary keys, back and forth, on a per-row basis. All steps slow each other down. Thus, you get a longer running query.

##CAVEAT

Adding a compound index on user_profile should speed things up:

ALTER TABLE user_profile ADD INDEX (user_profile_id,user_profile_id);

##Answer to question 1

This should do it for you

UPDATE user_activity a
INNER JOIN user_profile b 
USING (user_profile_id)
SET a.user_profile_id_int = b.user_profile_id_int;

This will work in MySQL. If you are not used to that JOIN syntax, do this:

UPDATE user_activity a
INNER JOIN user_profile b 
ON a.user_profile_id = b.user_profile_id
SET a.user_profile_id_int = b.user_profile_id_int;

Both should work.

##Answer to question 2

Your query, in theory, works. However, look at what it is doing:

A table scan on user_activity, an indexed lookup of the user_profile_id_int using the PRIMARY KEY of user_profile, and an in-place update of the current row in user_activity.

The query is hitting two tables and two primary keys, back and forth, on a per-row basis. All steps slow each other down. Thus, you get a longer running query.

##CAVEAT

Adding a compound index on user_profile should speed things up:

ALTER TABLE user_profile ADD INDEX (user_profile_id,user_profile_id_int);

This should do it for you

UPDATE user_activity a
INNER user_profile b 
USING (user_profile_id)
SET a.user_profile_id_int = b.user_profile_id_int;

This will work in MySQL. If you are not used to that JOIN syntax, do this:

UPDATE user_activity a
INNER user_profile b 
ON a.user_profile_id = b.user_profile_id
SET a.user_profile_id_int = b.user_profile_id_int;

Both should work.

##Answer to question 1

This should do it for you

UPDATE user_activity a
INNER JOIN user_profile b 
USING (user_profile_id)
SET a.user_profile_id_int = b.user_profile_id_int;

This will work in MySQL. If you are not used to that JOIN syntax, do this:

UPDATE user_activity a
INNER JOIN user_profile b 
ON a.user_profile_id = b.user_profile_id
SET a.user_profile_id_int = b.user_profile_id_int;

Both should work.

##Answer to question 2

Your query, in theory, works. However, look at what it is doing:

A table scan on user_activity, an indexed lookup of the user_profile_id_int using the PRIMARY KEY of user_profile, and an in-place update of the current row in user_activity.

The query is hitting two tables and two primary keys, back and forth, on a per-row basis. All steps slow each other down. Thus, you get a longer running query.

##CAVEAT

Adding a compound index on user_profile should speed things up:

ALTER TABLE user_profile ADD INDEX (user_profile_id,user_profile_id);