SQL Query Help Please

[godrob]

Hi guys, I am very new to SQL and could really need some help with an SQL Query please...

I need an SQL Query that will copy the column data of 'user_login' found in table 'wp_users' and replace the column of 'value' that is found in table 'wp_bp_xprofile_data' BUT only if the column of 'field_id' found in ' 'wp_bp_xprofile_data' = '1'

I hope that makes sense?

Any help greatly appreciated

Thanks in advance
Rob.

 

[PHV]

How is 'wp_users' related to 'wp_bp_xprofile_data' ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[godrob]

Hi there, thanks for your reply. I'm sorry, I'm new to this and don't understand what you mean by 'relationship'

If it helps, I can tell you that I have one database, with 97 tables, two of which are:

wp_users
wp_xprofile_data

wp_xprofile_data looks like this:

id | field_id | user_id | value
1 | 1 | 1 | john smith
2 | 2 | 1 | john@smith.com
3 | 3 | 1 | 123456
4 | 1 | 2 | joe bloggs
5 | 2 | 2 | joe@bloggs.com
6 | 3 | 2 | 987564

Does this help at all?

Thanks
Rob

 

[PHV]

And how looks wp_users ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[godrob]

Hi PHV,

The column names of the table 'wp_users' looks like this:

ID, user_login, user_pass, user_nicename, user_email, user_url, user_registered, user_activation_key, user_status, display_name, spam, deleted

Does that help?

Thanks
Rob.

 

[PHV]

So, wp_xprofile_data.user_id references wp_users.ID ?
If that is the case then you can try something like this:

UPDATE wp_xprofile_data
SET value=(SELECT user_login FROM wp_users WHERE ID=wp_xprofile_data.user_id)
WHERE field_id=1

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[godrob]

PH

Thank you so much!

That worked. You have just saved me hours of time, thank you!

Regards
Rob.