We help IT Professionals succeed at work.

SQL UPDATE instead of my Old FETCH Statement

724 Views
Last Modified: 2012-06-22
I am trying to re-write/translate a loop that I used in Access 2003 VBA to SQL Server 2008.  Initially I was going to use FETCH, but have been convinced that would not be the best way to accomplish my goal.  The related question that I started this process with is:

https://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SQL_Server_2008/Q_26216709.html

One of the experts in that question made a suggestion as to what should be my converted process using SQL.  He suggested the following:

1) insert into tblREO_Dates any rows from LnNum_Xref that dont have tblDailyPmntStatus
2) update tblREO_Dates.REO_DT with date time for "REO" from tblDailyPmntStatus
3) update tblREO_Dates.FC_FIRST with date time for "FC" from tblDailyPmntStatus
4) update tblREO_Dates.FC_CURR with date time for "FC" from tblDailyPmntStatus

Item # 3 will actually be an INSERT, and items 2 and 4 will be UPDATE statements.  I am currently working on item # 2 from the above list.

I have attached a SELECT query that creates the recordset needed to update the tblREO-Dates table.  In the query, I simply named the recordset with three columns; VCC_LnNum, Guarantor_Last, and REO_DT.  The field that would link tblREO_Dates(table to be updated) to my queried recordset(derived table???) is VCC_LnNum.  REO_DT is the field that needs to be updated using REO_DT from the recordset.  With what I have learned so far about the UPDATE statement, I can't figure out how to do this.

I have probably left info out, so just ask and I will get it to you ASAP.

Thanks - Hubbs


-- Below is the recordset containing values that need to be used to update the tblREO_Dates table

SELECT dbo.LnNum_Xref.VCC_LnNum, dbo.LnNum_Xref.Guarantor_Last, MIN(dbo.tblDailyPmntStatus.As_Of_Dt) AS REO_DT
FROM dbo.LnNum_Xref INNER JOIN
            dbo.tblDailyPmntStatus ON dbo.LnNum_Xref.Serv_LnNum = dbo.tblDailyPmntStatus.LOAN_NUM INNER JOIN
            dbo.tblREO_Dates ON dbo.LnNum_Xref.VCC_LnNum = dbo.tblREO_Dates.VCC_LnNum
WHERE     (dbo.tblDailyPmntStatus.STATUS = 'REO') AND (dbo.tblREO_Dates.VCC_LnNum IS NOT NULL) AND (dbo.tblREO_Dates.REO_Dt IS NULL)
GROUP BY dbo.LnNum_Xref.VCC_LnNum, dbo.LnNum_Xref.Guarantor_Last

Open in new window

Comment
Watch Question

Guy Hengel [angelIII / a3]Billing Engineer
CERTIFIED EXPERT
Most Valuable Expert 2014
Top Expert 2009

Commented:

Author

Commented:
Yes I am - but not quite sure how.  The training I received for UPDATE with a JOIN was a bit basic.  I will look at your link.

Thanks

Author

Commented:
Angellll - I looked through your link.  It appears as though 2 Valid is the code I should go with. (I could be wrong)  My problem is that my subquery is too complicated for me to work into the example that uses join.  I need guidance in taking what I already have, and work it into the format used in "2 Valid".

Thanks - Hubbs
Guy Hengel [angelIII / a3]Billing Engineer
CERTIFIED EXPERT
Most Valuable Expert 2014
Top Expert 2009

Commented:
as I am far from being able to understand your schema or the query you posted,
could you try to make it a update / join statement as you think it should do, and post that here, along with the error message(s) you get, resp the problems you get?
CERTIFIED EXPERT
Top Expert 2012
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION
CERTIFIED EXPERT
Top Expert 2012

Commented:
Incidentally, I find it odd that you do a GROUP BY on VCC_LnNum and Guarantor_Last, yet only JOIN using the the first column.  That tells me there is something wrong there.  But I could be wrong.

Author

Commented:
Ac,

the Guarantor_Last field was only included initially as I was experimenting with the original SELECT statement.  You are right in that it does not need to be there.  I am looking at your code now.  Thanks for the reply.

Hubbs

Author

Commented:
Ac,

Your code worked beautifully.  Thanks a ton for taking the time.  This is going to help me tremendously as I learn SQL.  I had to make one little change in the link.  On line 12 I had to change "1.VCC_LnNum" to "d.VCC_LnNum".  Would you agree with that?  I had gotten an error, and so I changed it, and it worked.   I don't use the SQL proprietary method of doing aliases, but I have seen it in some of my training videos.  Again - Thanks!

Hubbs

Author

Commented:
THANKS!
CERTIFIED EXPERT
Top Expert 2012

Commented:
>>On line 12 I had to change "1.VCC_LnNum" to "d.VCC_LnNum".  Would you agree with that? <<
Absolutely.  It should be "d".

>>I don't use the SQL proprietary method of doing aliases<<
It is standard SQL.  Use them it makes it a lot easier to read.

Author

Commented:
>> It is standard SQL.  Use them it makes it a lot easier to read. <<

Oh, okay - thanks.  My bad, I thought I had watched a video that said that method in SQL might not work in other environments.  No Big.  I surely agree that it is easier to read!
CERTIFIED EXPERT
Top Expert 2012

Commented:
>>My bad, I thought I had watched a video that said that method in SQL might not work in other environments<<
Since you are familiar with the SQL dialect used in MS Access, then you should know that the concept is practically the same.

Instead of:
SELECT  l.VCC_LnNum,
        l.Guarantor_Last,
        MIN(s.As_Of_Dt) REO_DT
FROM    dbo.LnNum_Xref l
        INNER JOIN dbo.tblDailyPmntStatus s ON l.Serv_LnNum = s.LOAN_NUM
WHERE   s.[Status] = 'REO'
GROUP BY l.VCC_LnNum,
        l.Guarantor_Last

The syntax for aliases is as follows (and this is also acceptable in T-SQL):
SELECT  l.VCC_LnNum,
        l.Guarantor_Last,
        MIN(s.As_Of_Dt) AS REO_DT
FROM    dbo.LnNum_Xref AS l
        INNER JOIN dbo.tblDailyPmntStatus AS s ON l.Serv_LnNum = s.LOAN_NUM
WHERE   s.[Status] = 'REO'
GROUP BY l.VCC_LnNum,
        l.Guarantor_Last

Author

Commented:
Thanks AC.  I appreciate you taking the time to teach/clarify.

Hubbs

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.