update records in same table with Matching ID
Here is the scenario...
I have one table with the following columns. I am trying to update the prior year fiscal records with this new year.
ID, Committe, Redetermination, Fiscal
If a record from this year has a fiscal = 2014 and Redetermination = 'X' Then any matching ID where last year fiscal = 2013 would need to update its Redetermination = 'Completed'
How can I do that in SQL?
I have one table with the following columns. I am trying to update the prior year fiscal records with this new year.
ID, Committe, Redetermination, Fiscal
If a record from this year has a fiscal = 2014 and Redetermination = 'X' Then any matching ID where last year fiscal = 2013 would need to update its Redetermination = 'Completed'
How can I do that in SQL?
ASKER
tblRegistration is ambiguous
UPDATE tblRegistration
SET Redetermination = 'Completed'
FROM tblRegistration AS tblRegistration13
INNER JOIN tblRegistration AS tblRegistration14
ON tblRegistration13.ID = tblRegistration14.ID
AND tblRegistration13.fiscal = 2013
AND tblRegistration14.fiscal = 2014
AND tblRegistration14.Redetermination = 'X'ASKER CERTIFIED SOLUTION
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
SOLUTION
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
ASKER
None of these are executing with any results...
ASKER
Would this work?
Update tblRegistraion2013 Set Redetermination = case when tblRegistration2014.fiscal = 2014 And Redetermination = 'X' And EntryTime = '07/01/2013' THEN 'Completed' else '' end FROM tblRegistration As tblRegistraion2014 INNER JOIN tblRegistration As tblRegistraion2013 on tblRegistraion2013.id=tblRegistraion2014.id
What do you get when you run the subquery?
SELECT R13.ID
FROM tblRegistration AS R13
INNER JOIN tblRegistration AS R14
ON R13.ID = R14.ID
AND R13.fiscal = 2013
AND R14.fiscal = 2014
AND R14.Redetermination = 'X'
SELECT R13.ID
FROM tblRegistration AS R13
INNER JOIN tblRegistration AS R14
ON R13.ID = R14.ID
AND R13.fiscal = 2013
AND R14.fiscal = 2014
AND R14.Redetermination = 'X'
Can you clarify what do you mean by
Do you get an error message? It's not updating?
Your query will have a different outcome... I would still tweak it to be like this:
None of these are executing with any results...
Do you get an error message? It's not updating?
Your query will have a different outcome... I would still tweak it to be like this:
Update tblRegistraion2013
Set tblRegistraion2013.Redetermination = case when tblRegistraion2014.fiscal = 2014 And
tblRegistraion2014.Redetermination = 'X' And
tblRegistraion2014.EntryTime = '07/01/2013'
THEN 'Completed'
else '' end
FROM tblRegistration As tblRegistraion2013
INNER JOIN tblRegistration As tblRegistraion2014 on tblRegistraion2013.id=tblRegistraion2014.idASKER
ralmada,
It is returning results. The results are zero records are affected.
It is returning results. The results are zero records are affected.
ASKER
BriCrowe,
That returns no results.
No errors
The problem is I know there should be results
That returns no results.
No errors
The problem is I know there should be results
ASKER
It worked. I needed to make a few changes.
SET Redetermination = 'Completed'
FROM MyTable AS MyTable 13
INNER JOIN MyTable AS MyTable 14
ON MyTable13.ID = MyTable14.ID
AND MyTable13.fiscal = 2013
AND MyTable14.fiscal = 2014
AND MyTable14.Redetermination = ''X'