August 15, 2017 at 9:38 am
Hello All,
I have the below statement and when it runs it just makes the field Overridenotes field null in the Update table
Update rskgov.FraudOpsDashboardVettingDataYTD a
Set OverrideNotes = (Select OverrideNotes From VETTINGRESULTS b Where a.AppNum = b.AppNum and a.category = b.category and a.FraudSpecialist = b.'Fraud Specialist'n and a.ADJSLAHours = b.Time and a.SLAEndDate = b.'SLA EndDate'n)
August 15, 2017 at 9:46 am
reggiete - Tuesday, August 15, 2017 9:38 AMHello All,I have the below statement and when it runs it just makes the field Overridenotes field null in the Update table
Update rskgov.FraudOpsDashboardVettingDataYTD a
Set OverrideNotes = (Select OverrideNotes From VETTINGRESULTS b Where a.AppNum = b.AppNum and a.category = b.category and a.FraudSpecialist = b.'Fraud Specialist'n and a.ADJSLAHours = b.Time and a.SLAEndDate = b.'SLA EndDate'n)
If they're updating the NULL, then that's the value that is being returned from your subquery.
What are you expecting it to do? We don't have access to you data, so we need more information. See the link in my signature on how to ask questions involving T-SQL.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
August 15, 2017 at 9:57 am
The other possibility is that the subquery is simply returning no rows, which would also result in the assignment of NULL.
Further, the query as posted wouldn't even execute, so I'm guessing you've changed some things from the original query (or you're showing us a query from some other database platform where that is correct syntax).
Cheers!
August 15, 2017 at 10:52 am
maybe it would be better to do this as a JOIN instead of a subquery:UPDATE a SET
OverrideNotes = b.OverrideNotes
FROM rskgov.FraudOpsDashboardVettingDataYTD a
INNER JOIN VETTINGRESULTS b ON a.AppNum = b.AppNum AND a.category = b.category AND a.FraudSpecialist = b.[Fraud Specialist] AND a.ADJSLAHours = b.Time AND a.SLAEndDate = b.[SLA EndDate]
it's best to investigate the data and figure out how many rows you expect to be updated before doing the update, so you can compare and make sure what is happening is what you think should be.
August 15, 2017 at 1:08 pm
What RDBMS are you using? ... AND a.FraudSpecialist = b.'Fraud Specialist'n isn't valid T-SQL syntax...
In any case, no matter what RDBMS you're using, it's typically helpful to script UPDATEs (and DELETEs) as a SELECT statement 1st in order to verify your changes before committing them...
I don't know if the following is valid syntax for your database but hopefully you'll get the idea and be able to modify as needed.
UPDATE fod SET
fod.OverrideNotes = vr.OverrideNotes
-- SELECT fod.OverrideNotes, vr.OverrideNotes
FROM
rskgov.FraudOpsDashboardVettingDataYTD fod
JOIN vrs.VETTINGRESULTS vr
ON fod.AppNum = vr.AppNum
AND fod.category = vr.category
AND fod.FraudSpecialist = vr.'Fraud Specialist'n -- ???
AND fod.ADJSLAHours = vr.Time
AND fod.SLAEndDate = vr.'SLA EndDate'n -- ???
;
August 22, 2017 at 10:25 pm
Seems like this is becoming more and more common and I don't get it. Why do people ask a question and then never come back when people ask for clarification?
--Jeff Moden
Change is inevitable... Change for the better is not.
August 22, 2017 at 11:42 pm
Jeff Moden - Tuesday, August 22, 2017 10:25 PMSeems like this is becoming more and more common and I don't get it. Why do people ask a question and then never come back when people ask for clarification?
Sometimes it is just easier to hit and run than to interact?
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
August 22, 2017 at 11:53 pm
Jason A. Long - Tuesday, August 15, 2017 1:08 PMWhat RDBMS are you using? ... AND a.FraudSpecialist = b.'Fraud Specialist'n isn't valid T-SQL syntax...
In any case, no matter what RDBMS you're using, it's typically helpful to script UPDATEs (and DELETEs) as a SELECT statement 1st in order to verify your changes before committing them...
I don't know if the following is valid syntax for your database but hopefully you'll get the idea and be able to modify as needed.
UPDATE fod SET
fod.OverrideNotes = vr.OverrideNotes
-- SELECT fod.OverrideNotes, vr.OverrideNotes
FROM
rskgov.FraudOpsDashboardVettingDataYTD fod
JOIN vrs.VETTINGRESULTS vr
ON fod.AppNum = vr.AppNum
AND fod.category = vr.category
AND fod.FraudSpecialist = vr.'Fraud Specialist'n -- ???
AND fod.ADJSLAHours = vr.Time
AND fod.SLAEndDate = vr.'SLA EndDate'n -- ???
;
vr.'Fraud Specialist'n
That is a mysql - ism.
AFAIK, it should also be a ` instead of a '
More on the backtick or `
Identifiers can be quoted (delimited) within backtick characters (‘``’), which permits use of any character except a NUL byte or Unicode supplementary characters (U+10000U+10000 and up)
As for the n suffix, I don't know where that is coming from. Probably a translation mistake???
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
August 23, 2017 at 7:06 am
Jeff Moden - Tuesday, August 22, 2017 10:25 PMSeems like this is becoming more and more common and I don't get it. Why do people ask a question and then never come back when people ask for clarification?
Probably because they asked on Stack overflow, Connect, Microsoft Forums, and any other website where you could ask a support question. They got an answer on one of somewhere and left it there. The site that didn't get the "winning" answer is left in the dark.
The other thing that bugs me is not knowing if the solutions proposed actually worked. Some of the questions are a little more complex (this one not so much), but you see some really good answers but as no sample data/DDL was ever supplied, you have no idea if any of them were of any use; meaning they might not be much use to anyone. 🙁
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
August 23, 2017 at 7:12 am
Thom A - Wednesday, August 23, 2017 7:06 AMJeff Moden - Tuesday, August 22, 2017 10:25 PMSeems like this is becoming more and more common and I don't get it. Why do people ask a question and then never come back when people ask for clarification?Probably because they asked on Stack overflow, Connect, Microsoft Forums, and any other website where you could ask a support question. They got an answer on one of somewhere and left it there. The site that didn't get the "winning" answer is left in the dark.
The other thing that bugs me is not knowing if the solutions proposed actually worked. Some of the questions are a little more complex (this one not so much), but you see some really good answers but as no sample data/DDL was ever supplied, you have no idea if any of them were of any use; meaning they might not be much use to anyone. 🙁
August 23, 2017 at 7:12 am
Thom A - Wednesday, August 23, 2017 7:06 AMJeff Moden - Tuesday, August 22, 2017 10:25 PMSeems like this is becoming more and more common and I don't get it. Why do people ask a question and then never come back when people ask for clarification?Probably because they asked on Stack overflow, Connect, Microsoft Forums, and any other website where you could ask a support question. They got an answer on one of somewhere and left it there. The site that didn't get the "winning" answer is left in the dark.
The other thing that bugs me is not knowing if the solutions proposed actually worked. Some of the questions are a little more complex (this one not so much), but you see some really good answers but as no sample data/DDL was ever supplied, you have no idea if any of them were of any use; meaning they might not be much use to anyone. 🙁
August 23, 2017 at 7:13 am
Thanks everyone. Sorry I never responded. But the answers above fixed my issue. Thanks everyone
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply