SQL Query Help Please

Question

SQL Query Help Please

godrob (TechnicalUser)

(OP)

17 Jul 11 17:36

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 (MIS) · Answer 1 · 2011-07-18 03:11:18

How is 'wp_users' related to 'wp_bp_xprofile_data' ?

Hope This Helps, PH.
FAQ219-2884: How Do I Get Great Answers To my Tek-Tips Questions?
FAQ181-2886: How can I maximize my chances of getting an answer?

godrob (TechnicalUser) · Answer 2 · 2011-07-18 04:34:50

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 (MIS) · Answer 3 · 2011-07-18 04:44:58

And how looks wp_users ?

Hope This Helps, PH.
FAQ219-2884: How Do I Get Great Answers To my Tek-Tips Questions?
FAQ181-2886: How can I maximize my chances of getting an answer?

godrob (TechnicalUser) · Answer 4 · 2011-07-18 04:57:47

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 (MIS) · Answer 5 · 2011-07-18 05:05:46

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

CODE

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: How Do I Get Great Answers To my Tek-Tips Questions?
FAQ181-2886: How can I maximize my chances of getting an answer?

godrob (TechnicalUser) · Answer 6 · 2011-07-18 05:44:29

PH

Thank you so much!

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

Regards
Rob.

Come Join Us!

Posting Guidelines

SQL Query Help Please