February 14, 2014 at 8:52 am
I am trying to Write an update string for individual partID's. I wrote this query below but it isn't populating the time to test.
SELECT 'UPDATE Parts SET = [TimeToTest]' + ' ' +
Convert(varchar, (select test From [dbo].[db_PartsCats] as c Join Parts As P on P.category = C.CatID Where PartID = 48727))
+ ' ' + 'WHERE PartID = ' + CONVERT(varchar, P.PartID)
From Parts As P
Where FRID = 0 And TimeToTest = 0 and TimeToInstall = 0 and TimeToProgram = 0 And TimeToTrain = 0 And manufacturer = 187
Order By category
My results:
Should get UPDATE Parts SET = [TimeToTest] 0.5000 WHERE PartID = 48871 But getting Nulls instead
Any help would be appricated
February 14, 2014 at 9:38 am
As a side note, is your syntax not incorrect for the UPDATE statement?
UPDATEParts
SET[TimeToTest] = 0.5000
WHEREPartID = 48871;
February 14, 2014 at 9:41 am
Dohsan (2/14/2014)
As a side note, is your syntax not incorrect for the UPDATE statement?
UPDATEParts
SET[TimeToTest] = 0.5000
WHEREPartID = 48871;
I understand that but I don't want to write 743 individual query's. That's why I am trying to do it as a string instead because they all have different Test Times.
February 14, 2014 at 10:05 am
Why are you using dynamic SQL - why not something like this? I haven't checked the exact syntax, but you get the idea.
UPDATE Parts SET [TimeToTest]=
Convert(varchar, (select test From [dbo].[db_PartsCats] as c Join Parts As P on P.category = C.CatID Where PartID = 48727))
WHERE PartID = CONVERT(varchar, P.PartID)
AND FRID = 0 And TimeToTest = 0 and TimeToInstall = 0 and TimeToProgram = 0 And TimeToTrain = 0 And manufacturer = 187
AND PartID = CONVERT(varchar, P.PartID)
John
February 14, 2014 at 2:20 pm
If you gave us some sample data and DDL we could likely make this much simpler.
Your example doesn't really tell us all we need to know.
I imagine what you want will look something like
UPDATE p SET [TimeToTest] = c.test
FROM PARTS p
JOIN [dbo].[db_PartsCats] c
ON P.category = C.CatID
WHERE FRID = 0 And TimeToTest = 0 and TimeToInstall = 0 and TimeToProgram = 0 And TimeToTrain = 0 And manufacturer = 187
But I could be wrong. You might really want to get the category test time from one part and apply it to all parts. If you showed us an example, we could advise much better. But yeah, I'd be shocked if you need dynamic SQL.
February 14, 2014 at 2:27 pm
Thanks for everything guys I actually finally got the update query to work. Thanks for all you help, I do appreciate everything
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply