Update Query

Experts,

I need to confirm what this update query says.
The where condition is saying: dont update if ([import-CSM2].LCID) IS NOT IN [tblLetterOfCredit].[LetterOfCreditID]  but it will update if ([import-CSM2].LCID) IS IN [tblLetterOfCredit].[LetterOfCreditID]

I hope that makes sense.  


UPDATE [import-CSM2] INNER JOIN tblLetterOfCredit ON [import-CSM2].[Reference Number] = tblLetterOfCredit.LCNo SET [import-CSM2].LCID = [tblLetterOfCredit].[LetterOfCreditID]
WHERE ((([import-CSM2].LCID) Not In (SELECT [tblLetterOfCredit].[LetterOfCreditID] From [tblLetterOfCredit])) AND (([import-CSM2].[Actual Status])<>"GEC"));
pdvsaProject financeAsked:
Who is Participating?
 
mbizupConnect With a Mentor Commented:
Yup -- copy/paste issues.  I got the SELECT in there by mistake.

Try this:

UPDATE [import-CSM2] INNER JOIN tblLetterOfCredit 
ON [import-CSM2].[Reference Number] = tblLetterOfCredit.LCNo
AND [import-CSM2].LCID  = [tblLetterOfCredit].[LetterOfCreditID]
 SET [import-CSM2].LCID = [tblLetterOfCredit].[LetterOfCreditID]
WHERE [import-CSM2].[Actual Status] <>"GEC"

Open in new window

0
 
mbizupCommented:
I believe you've got things backwards.

Your query as is WILL update if

[import-CSM2].LCID Is Not found In [tblLetterOfCredit].[LetterOfCreditID]
0
 
becraigCommented:
It is saying update ONLY if the following two conditions are met:
1. import-CSM2].[Actual Status])<>"GEC"
2. import-CSM2].LCID is not present in SELECT [tblLetterOfCredit].[LetterOfCreditID] From [tblLetterOfCredit]
0
Cloud Class® Course: CompTIA Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

 
mbizupCommented:
Try this instead:

UPDATE [import-CSM2] INNER JOIN tblLetterOfCredit ON [import-CSM2].[Reference Number] = tblLetterOfCredit.LCNo
AND [import-CSM2].LCID  = SELECT [tblLetterOfCredit].[LetterOfCreditID]
 SET [import-CSM2].LCID = [tblLetterOfCredit].[LetterOfCreditID]

Open in new window

0
 
mbizupCommented:
Correction to include the <> GEC:

UPDATE [import-CSM2] INNER JOIN tblLetterOfCredit ON [import-CSM2].[Reference Number] = tblLetterOfCredit.LCNo
AND [import-CSM2].LCID  = SELECT [tblLetterOfCredit].[LetterOfCreditID]
SET [import-CSM2].LCID = [tblLetterOfCredit].[LetterOfCreditID]
WHERE [import-CSM2].[Actual Status] <>"GEC"

Open in new window

0
 
pdvsaProject financeAuthor Commented:
mbizup:  

Do you see a syntax in the response right above?  It says there is one.  It highlights [import-CSM2].[Reference Number]  on the first line.  

thanks...
0
 
mbizupCommented:
Or try this:

UPDATE [import-CSM2] INNER JOIN tblLetterOfCredit ON [import-CSM2].[Reference Number] = tblLetterOfCredit.LCNo SET [import-CSM2].LCID = [tblLetterOfCredit].[LetterOfCreditID]
WHERE [import-CSM2].LCID IN (SELECT [tblLetterOfCredit].[LetterOfCreditID] From [tblLetterOfCredit]) AND [import-CSM2].[Actual Status] <>"GEC";

Open in new window

0
 
pdvsaProject financeAuthor Commented:
perfect.   These queries are difficult.  Both SQL's returned same number of records.

thank you!
0
 
mbizupCommented:
If they both worked okay, I would opt for the first... I believe it would be better performance-wise.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.