May 26, 2015 at 11:05 am
Hello,
I am trying to update a table called CardioStaffClinical which has a field called LightsonLongname which is currently blank
Part of the query is comparing First Name and Last name with another table called Lightson which has a field called Name (Username): and getting the match
I am getting an error on
SET Lightsonlongname = b."Name (Username):"
Error: The multi-part identifier "b."Name (Username):" could not be bound
Full query is:
UPDATE dbo.CardioStaffClinical
SET Lightsonlongname = b."Name (Username):"
WHERE
(
select distinct
[Forename]AS First_Name1
,[Surname ]AS Last_Name1
FROM dbo.CardioStaffClinical
)AS a
JOIN
(
select distinct
"Name (Username):"
--, charindex (' , ',"Name (Username):",1)ASKey_
, SUBSTRING ("Name (Username):",1,charindex (' , ',"Name (Username):",1)-1)ASLast_Name2
--, SUBSTRING ("Name (Username):",(charindex (' , ',"Name (Username):",1)+3),50)ASFirst_Middle_Name
--, charindex (' ',SUBSTRING ("Name (Username):",(charindex (' , ',"Name (Username):",1)+3),50),1) AS Key2_
, SUBSTRING (SUBSTRING ("Name (Username):",(charindex (' , ',"Name (Username):",1)+3),50),1,
charindex (' ',SUBSTRING ("Name (Username):",(charindex (' , ',"Name (Username):",1)+3),50),1)-1) AS First_Name2
from lightson
Where "Name (Username):" like '% , %'
)AS b
ONa.First_Name1= b.First_Name2
ANDa.Last_Name1= b.Last_Name2
Hope you can help
May 26, 2015 at 11:27 am
I'm not understanding your usage of that WHERE clause.
The syntax for a joined update is basically the following:
UPDATE A
SET ..
FROM A
JOIN B ..
WHERE ..
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
May 26, 2015 at 11:27 am
DBA2015 (5/26/2015)
Hello,I am trying to update a table called CardioStaffClinical which has a field called LightsonLongname which is currently blank
Part of the query is comparing First Name and Last name with another table called Lightson which has a field called Name (Username): and getting the match
I am getting an error on
SET Lightsonlongname = b."Name (Username):"
Error: The multi-part identifier "b."Name (Username):" could not be bound
Full query is:
UPDATE dbo.CardioStaffClinicalSET Lightsonlongname = b."Name (Username):"
WHERE
(
select distinct
[Forename]AS First_Name1
,[Surname ]AS Last_Name1
FROM dbo.CardioStaffClinical
)AS a
JOIN
(
select distinct
"Name (Username):"
--, charindex (' , ',"Name (Username):",1)ASKey_
, SUBSTRING ("Name (Username):",1,charindex (' , ',"Name (Username):",1)-1)ASLast_Name2
--, SUBSTRING ("Name (Username):",(charindex (' , ',"Name (Username):",1)+3),50)ASFirst_Middle_Name
--, charindex (' ',SUBSTRING ("Name (Username):",(charindex (' , ',"Name (Username):",1)+3),50),1) AS Key2_
, SUBSTRING (SUBSTRING ("Name (Username):",(charindex (' , ',"Name (Username):",1)+3),50),1,
charindex (' ',SUBSTRING ("Name (Username):",(charindex (' , ',"Name (Username):",1)+3),50),1)-1) AS First_Name2
from lightson
Where "Name (Username):" like '% , %'
)AS b
ONa.First_Name1= b.First_Name2
ANDa.Last_Name1= b.Last_Name2
Hope you can help
A couple of things leap out.
1st, what a crazy column name. Spaces, parens, colons all make for a "hard to work with" variable name
2nd, alias the variable in the JOIN
select distinct
"Name (Username):" OldUserName
, etc. . .
then use the alias in the update
SET Lightsonlongname = b.OldUserName
If this does not solve the immediate issue, posting DDL and some data would go a long way to finding the answer.
__________________________________________________________________________________________________________
How to Post to get the most: http://www.sqlservercentral.com/articles/Best+Practices/61537/
May 26, 2015 at 12:38 pm
I suspect that the original poster meant to use FROM in place of WHERE, but given the code, I'm fairly sure that there's a much easier way to code this update. Yes, the column names are horrific, but that's not necessarily a total disaster. Take a look at the following and see if it might be a more practical option:
UPDATE CSC
SET CSC.Lightsonlongname = L.[Name (Username):]
FROM dbo.CardioStaffClinical AS CSC
INNER JOIN lightson AS L
ON CSC.Forename = SUBSTRING(SUBSTRING(L.[Name (Username):], (CHARINDEX(' , ', L.[Name (Username):], 1) + 3), 50), 1,
CHARINDEX(' ', SUBSTRING(L.[Name (Username):], (CHARINDEX(' , ', L.[Name (Username):], 1) + 3), 50), 1) - 1)
AND CSC.[Surname ] = SUBSTRING(L.[Name (Username):], 1, CHARINDEX(' , ', L.[Name (Username):], 1) - 1)
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
May 26, 2015 at 12:54 pm
Basically I am trying to convert the below Select query to an Update query where it updates the field "LightsonLongname" in the CardioStaffClinical using the results from the JOIN query, hope you can help
SELECT
a.First_Name1
b.First_Name2
a.Last_Name1
b.Last_Name2
FROM
(
select distinct
[Forename]AS First_Name1
,[Surname ]AS Last_Name1
FROM dbo.CardioStaffClinical
)AS a
JOIN
(
select distinct
"Name (Username):"
--, charindex (' , ',"Name (Username):",1)ASKey_
, SUBSTRING ("Name (Username):",1,charindex (' , ',"Name (Username):",1)-1)ASLast_Name2
--, SUBSTRING ("Name (Username):",(charindex (' , ',"Name (Username):",1)+3),50)ASFirst_Middle_Name
--, charindex (' ',SUBSTRING ("Name (Username):",(charindex (' , ',"Name (Username):",1)+3),50),1) AS Key2_
, SUBSTRING (SUBSTRING ("Name (Username):",(charindex (' , ',"Name (Username):",1)+3),50),1,
charindex (' ',SUBSTRING ("Name (Username):",(charindex (' , ',"Name (Username):",1)+3),50),1)-1) AS First_Name2
from lightson
Where "Name (Username):" like '% , %'
)AS b
ONa.First_Name1= b.First_Name2
ANDa.Last_Name1= b.Last_Name2
May 26, 2015 at 12:59 pm
Thanks Steve, will give this a go tomorrow!
May 27, 2015 at 3:29 am
sgmunson (5/26/2015)
I suspect that the original poster meant to use FROM in place of WHERE, but given the code, I'm fairly sure that there's a much easier way to code this update. Yes, the column names are horrific, but that's not necessarily a total disaster. Take a look at the following and see if it might be a more practical option:
UPDATE CSC
SET CSC.Lightsonlongname = L.[Name (Username):]
FROM dbo.CardioStaffClinical AS CSC
INNER JOIN lightson AS L
ON CSC.Forename = SUBSTRING(SUBSTRING(L.[Name (Username):], (CHARINDEX(' , ', L.[Name (Username):], 1) + 3), 50), 1,
CHARINDEX(' ', SUBSTRING(L.[Name (Username):], (CHARINDEX(' , ', L.[Name (Username):], 1) + 3), 50), 1) - 1)
AND CSC.[Surname ] = SUBSTRING(L.[Name (Username):], 1, CHARINDEX(' , ', L.[Name (Username):], 1) - 1)
Hi Steve,
When I run the query I am getting:
Msg 537, Level 16, State 3, Line 1
Invalid length parameter passed to the LEFT or SUBSTRING function.
The statement has been terminated.
Hope you can help
May 27, 2015 at 5:14 am
-- ALWAYS figure out a SELECT to test the update.
SELECT
b.*, a.*
FROM dbo.CardioStaffClinical a
INNER JOIN (
SELECT
[Name (Username):],
Last_Name2 = SUBSTRING([Name (Username):], 1, x.CommaPos-1),
First_Name2 = SUBSTRING([Name (Username):], x.CommaPos+3, 8000)
FROM (SELECT [Name (Username):] = 'Smith , John') l
CROSS APPLY (SELECT CommaPos = CHARINDEX(' , ',l.[Name (Username):],1)) x
) b
ON b.First_Name2 = a.[Forename]
AND b.Last_Name2 = a.[Surname ]
-- THEN convert the SELECT to UPDATE
UPDATE a SET
Lightsonlongname = b.[Name (Username):]
FROM dbo.CardioStaffClinical a
INNER JOIN (
SELECT
[Name (Username):],
Last_Name2 = SUBSTRING([Name (Username):], 1, x.CommaPos-1),
First_Name2 = SUBSTRING([Name (Username):], x.CommaPos+3, 8000)
FROM (SELECT [Name (Username):] = 'Smith , John') l
CROSS APPLY (SELECT CommaPos = CHARINDEX(' , ',l.[Name (Username):],1)) x
) b
ON b.First_Name2 = a.[Forename]
AND b.Last_Name2 = a.[Surname ]
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
May 27, 2015 at 9:37 am
DBA2015 (5/27/2015)
sgmunson (5/26/2015)
I suspect that the original poster meant to use FROM in place of WHERE, but given the code, I'm fairly sure that there's a much easier way to code this update. Yes, the column names are horrific, but that's not necessarily a total disaster. Take a look at the following and see if it might be a more practical option:
UPDATE CSC
SET CSC.Lightsonlongname = L.[Name (Username):]
FROM dbo.CardioStaffClinical AS CSC
INNER JOIN lightson AS L
ON CSC.Forename = SUBSTRING(SUBSTRING(L.[Name (Username):], (CHARINDEX(' , ', L.[Name (Username):], 1) + 3), 50), 1,
CHARINDEX(' ', SUBSTRING(L.[Name (Username):], (CHARINDEX(' , ', L.[Name (Username):], 1) + 3), 50), 1) - 1)
AND CSC.[Surname ] = SUBSTRING(L.[Name (Username):], 1, CHARINDEX(' , ', L.[Name (Username):], 1) - 1)
Hi Steve,
When I run the query I am getting:
Msg 537, Level 16, State 3, Line 1
Invalid length parameter passed to the LEFT or SUBSTRING function.
The statement has been terminated.
Hope you can help
I had assumed your existing query was operational, but it now appears that it is not. Without data to work with, it would be difficult to determine where the problem is. However, clearly, one of the string functions is getting an invalid value, so you might want to expose the values that are being supplied, and check for negative numbers or possibly 0, where they don't belong. Once you know WHY the problem occurs, you can determine the appropriate replacement logic for that query. ChrisM has a good suggestion...
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
May 27, 2015 at 9:52 am
sgmunson (5/27/2015)
DBA2015 (5/27/2015)
sgmunson (5/26/2015)
I suspect that the original poster meant to use FROM in place of WHERE, but given the code, I'm fairly sure that there's a much easier way to code this update. Yes, the column names are horrific, but that's not necessarily a total disaster. Take a look at the following and see if it might be a more practical option:
UPDATE CSC
SET CSC.Lightsonlongname = L.[Name (Username):]
FROM dbo.CardioStaffClinical AS CSC
INNER JOIN lightson AS L
ON CSC.Forename = SUBSTRING(SUBSTRING(L.[Name (Username):], (CHARINDEX(' , ', L.[Name (Username):], 1) + 3), 50), 1,
CHARINDEX(' ', SUBSTRING(L.[Name (Username):], (CHARINDEX(' , ', L.[Name (Username):], 1) + 3), 50), 1) - 1)
AND CSC.[Surname ] = SUBSTRING(L.[Name (Username):], 1, CHARINDEX(' , ', L.[Name (Username):], 1) - 1)
Hi Steve,
When I run the query I am getting:
Msg 537, Level 16, State 3, Line 1
Invalid length parameter passed to the LEFT or SUBSTRING function.
The statement has been terminated.
Hope you can help
I had assumed your existing query was operational, but it now appears that it is not. Without data to work with, it would be difficult to determine where the problem is. However, clearly, one of the string functions is getting an invalid value, so you might want to expose the values that are being supplied, and check for negative numbers or possibly 0, where they don't belong. Once you know WHY the problem occurs, you can determine the appropriate replacement logic for that query. ChrisM has a good suggestion...
I added Where "Name (Username):" like '% , %' at the end and it now works, cheers Steve
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply