SQL: Move column data to other table in same relation

I'm wondering if it's possible to move all data from one column in table to another 开发者_开发百科table. Here's what i'm trying to do:

Table 1 : users - columns trying to read+move = oauth_access_key and oauth_access_secret

Table 2 : account_users - target columns: oauth_token_key, oauth_token_secret

The relation key between these tables is "user_id".

Is this possible in one query? I know this is easily done in PHP, but i'm wondering if this can be done in plain SQL.

Thanks in advance.

---

UPDATE users, account_users 
SET account_users.oauth_token_key=users.oauth_access_key,  
    account_users.oauth_token_secret = users.oauth_access_secret
WHERE account_users.user_id=users.user_id;

You can use JOIN syntax on MySQL Update.

---

I think the answer you are looking for is

INSERT INTO `account_users` (`user_id`, `oauth_token_key`, `oauth_token_secret`)
SELECT `user_id`, `oauth_access_key`, `oauth_access_secret` FROM `user`
ON DUPLICATE KEY UPDATE 
  `oauth_token_key` = VALUES(`oauth_token_key`),
  `oauth_token_secret` = VALUES(`oauth_token_secret`);

EDIT

I am assuming you want to move data as in 'put it somewhere it hasn't been yet'.

EDIT2

Here is a documentation on VALUES(): http://dev.mysql.com/doc/refman/5.0/en/miscellaneous-functions.html#function_values

---

Since the title is SQL, and not DB specific... here's a solution for those who stumble upon this while searching for Postgres:

UPDATE account_users        
SET oauth_token_key = users.oauth_access_key,  
    oauth_token_secret = users.oauth_access_secret
FROM profiles
WHERE account_users.user_id = users.user_id;

---

INSERT INTO account_users (user_id, oauth_token_secret) 
SELECT users.user_id, users.oauth_access_secret FROM users 
ON DUPLICATE KEY UPDATE account_users.oauth_token_secret = users.oauth_access_secret