Link to home
Start Free TrialLog in
Avatar of al4629740
al4629740Flag for United States of America

asked on

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?
Avatar of Brian Crowe
Brian Crowe
Flag of United States of America image

UPDATE MyTable
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'
Avatar of al4629740

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'

Open in new window

ASKER CERTIFIED SOLUTION
Avatar of Brian Crowe
Brian Crowe
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
None of these are executing with any results...
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

Open in new window

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'
Can you clarify what do you mean by
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.id

Open in new window

ralmada,

It is returning results.  The results are zero records are affected.
BriCrowe,

That returns no results.

No errors

The problem is I know there should be results
It worked.  I needed to make a few changes.