Help With SQl Query Please - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Help With SQl Query Please

Author

Topic

godrob
Starting Member

5 Posts

Posted - 2011-07-17 : 17:03:16

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.

ebingeorge.sqlserver
Starting Member

7 Posts

Posted - 2011-07-17 : 17:09:04

Hi Rob,

Hope this query helps..

UPDATE wpusers
SET user_login = wpd.value
FROM wpusers wp
INNER JOIN ON wp_bp_xprofile_data wpd
ON wp.UserID = wpd.UserID
WHERE wpd.field_id = 1

godrob
Starting Member

5 Posts

Posted - 2011-07-17 : 17:22:04

Thanks for your quick relply...

Unfortuantely, I get the error of:

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'FROM wp_users INNER JOIN ON wp_bp_xprofile_data wpd ON wp.UserID = wpd.UserID ' at line 3

Any ideas?

Thanks for your help
Rob.

ebingeorge.sqlserver
Starting Member

7 Posts

Posted - 2011-07-17 : 17:35:56

This should work in SQL Server. (not sure of MySQL).
But it seems you have not added the alias 'wp' after 'FROM wp_users'

quote:
Originally posted by godrob

Thanks for your quick relply...

Unfortuantely, I get the error of:

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'FROM wp_users INNER JOIN ON wp_bp_xprofile_data wpd ON wp.UserID = wpd.UserID ' at line 3

Any ideas?

Thanks for your help
Rob.

ebingeorge.sqlserver
Starting Member

7 Posts

Posted - 2011-07-17 : 17:38:38

Sorry.. my bad,

UPDATE wpusers
SET user_login = wpd.value
FROM wpusers wp
INNER JOIN wp_bp_xprofile_data wpd
ON wp.UserID = wpd.UserID
WHERE wpd.field_id = 1

This should work in SQL Server.. (no ON after INNER JOIN)

:)

godrob
Starting Member

5 Posts

Posted - 2011-07-17 : 17:40:13

Hi,

I copied and pasted your code. Shouldn't wpusers be wp_users?

Also, I don't see any refernce to the column 'Value' in the query

Thanks
Rob

godrob
Starting Member

5 Posts

Posted - 2011-07-17 : 17:44:02

just tried again with your new query and got this again:

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'FROM wp_users wp INNER JOIN wp_bp_xprofile_data wpd ON wp.UserID = wpd.UserID ' at line 3

Thanks
Rob

ebingeorge.sqlserver
Starting Member

7 Posts

Posted - 2011-07-17 : 17:44:31

Hi, Rob,

Yes it should be wp_users.
But copy -paste wont work unless there is a relationship
between the two tables which I have assumed. (UserID).

SET user_login = wpd.value
(The column 'Value' that you need to replace.)

quote:
Originally posted by godrob

Hi,

I copied and pasted your code. Shouldn't wpusers be wp_users?

Also, I don't see any refernce to the column 'Value' in the query

Thanks
Rob

godrob
Starting Member

5 Posts

Posted - 2011-07-17 : 17:54:26

I'm sorry, I don't understand how to create a relationship between the two tables.

Can you pleae explain in simpe terms what I need to do to get this to work?

Thanks for your help
Rob

ebingeorge.sqlserver
Starting Member

7 Posts

Posted - 2011-07-17 : 18:13:11

Hi Rob,

You need to provide more details
(all columns available in wp_users ,wp_bp_xprofile_data etc.)

Please Note : If you are using MySQL pls try posting this in a MySQL Forum..

You can get the relationship between the two tables using the below query

WITH TestTable as(
Select
object_name(rkeyid) Parent_Table,
object_name(fkeyid) Child_Table,
object_name(constid) FKey_Name,
c1.name FKey_Col,
c2.name Ref_KeyCol
From
sys.sysforeignkeys s
Inner join sys.syscolumns c1
on ( s.fkeyid = c1.id And s.fkey = c1.colid )
Inner join syscolumns c2
on ( s.rkeyid = c2.id And s.rkey = c2.colid )
)
SELECT * from TestTable where Parent_Table like '%wp_users%'
and Child_Table like '%wp_bp_xprofile_data%'

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-07-17 : 20:36:00

#14613 - You have posted in a SQL Server site. Please post your MySQL question on dbforums.com or forums.mysql.com

quote:
Originally posted by godrob

just tried again with your new query and got this again:

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'FROM wp_users wp INNER JOIN wp_bp_xprofile_data wpd ON wp.UserID = wpd.UserID ' at line 3

Thanks
Rob

KH
[spoiler]Time is always against us[/spoiler]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources