CODEWITHSUNDEEP
mysqlsql
byteshift
byteshift

Reputation: 205

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.

Upvotes: 17

Views: 30024

Answers (4)

Neal
Neal

Reputation: 4568

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;

Upvotes: 5

Mike
Mike

Reputation: 2587

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

Upvotes: 10

PtPazuzu
PtPazuzu

Reputation: 2537

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

Upvotes: 0

Jacob
Jacob

Reputation: 43229

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.

Upvotes: 30

Related Questions