Solved

Update Query

Posted on 2013-01-20
9
279 Views
Last Modified: 2013-01-21
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"));
0
Comment
Question by:pdvsa
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 2
9 Comments
 
LVL 61

Expert Comment

by:mbizup
ID: 38799643
I believe you've got things backwards.

Your query as is WILL update if

[import-CSM2].LCID Is Not found In [tblLetterOfCredit].[LetterOfCreditID]
0
 
LVL 29

Expert Comment

by:becraig
ID: 38799644
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
 
LVL 61

Expert Comment

by:mbizup
ID: 38799648
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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 61

Expert Comment

by:mbizup
ID: 38799652
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
 

Author Comment

by:pdvsa
ID: 38799660
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
 
LVL 61

Accepted Solution

by:
mbizup earned 500 total points
ID: 38799693
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
 
LVL 61

Expert Comment

by:mbizup
ID: 38799703
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
 

Author Comment

by:pdvsa
ID: 38799727
perfect.   These queries are difficult.  Both SQL's returned same number of records.

thank you!
0
 
LVL 61

Expert Comment

by:mbizup
ID: 38799759
If they both worked okay, I would opt for the first... I believe it would be better performance-wise.
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
MS ACCESS VBA FORMATTING 9 63
Access Need to add combo box to sub form 10 51
Add Underline to custom Caption on Label 4 36
Using a combo box to search a form. 3 36
As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

751 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question