• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 151
  • Last Modified:

Update Query from two tables based on a condition

I have two tables that I need to pull information from but only when a condition occurs in one of the tables.

When the field in SortYesNo = "Yes" in QREVALUE, I want to update the table SortDetail with the fields listed below.

The fields that I want to pull from QREVALUE are
ScrapRecordTag
TagNumber
QPRQPINumber
ProblemDescription
QREConfirmation
NAMC
SortYesNo

The fields that I want to pull from SkpiProblemLog
PartName
PartNumber

ScrapRecordTag is the primary key and is in all three tables.

I have never done anything like this and I really don't know if it is possible.
0
ggodwin
Asked:
ggodwin
1 Solution
 
HuaMinChenBusiness AnalystCommented:
You can have an Exists check for the condition and then do the update.
0
 
Kelvin SparksCommented:
What database type are we talking about here - Access, SQL Server, Oracle.....

Each can do but the syntax is quite different for each.


Kelvin
0
 
ggodwinAuthor Commented:
Oh I am sorry,
Access 2003
0
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.

 
sammySeltzerCommented:
I don't think you can do what you are trying to do in one statement.

You will have to use Transaction, something like:


BEGIN TRANSACTION

UPDATE
    QREVALUE 
SET
ScrapRecordTag = 'Some value',
TagNumber = 'some value',
QPRQPINumber = 'some value',
ProblemDescription = 'some value',
QREConfirmation ='some value'
NAMC = 'some value
FROM QREVALUE  Q1, SkpiProblemLog S2
where SortYesNo='Yes' and Q1.ScrapRecordTag  = S2.ScrapRecordTag 

UPDATE
    SkpiProblemLog 
SET
PartName= 'Some value',
PartNumber= 'some value',

FROM  SkpiProblemLog S2, QREVALUE  Q1
where Q1.ScrapRecordTag  = S2.ScrapRecordTag 

COMMIT

Open in new window

0
 
ggodwinAuthor Commented:
OK, I really don't  want to create a new method in this database. I have decided that I can achieve the same goal by creating two queries and running them separate.

I want to query and Insert records from QREVALUE into SORT. However, I do not want to insert old records again. If the record already exist then there is NO need to re-insert the data. Also, I only want to query records in QREVALUE where Sort (Yes/No) field marked as "Yes".

I started with the below code but now I'm lost.

INSERT INTO Sort ( QPRQPINumber, TagNumber, NAMC, ScrapRecordTag )
SELECT QREVALUE.[QPR/QPI Number], QREVALUE.[Tag Number], QREVALUE.NAMC, QREVALUE.ScrapRecordTag  AS Expr1
FROM QREVALUE LEFT JOIN Sort ON QREVALUE.[Tag Number]=Sort.TagNumber
WHERE (((QREVALUE.TagNumber) Is Null));

Open in new window

0
 
sammySeltzerCommented:
First, from that query, you seem to be walking away from your original specs.

You seem to be doing an INSERT statement instead of an UPDATE statement.

Second, you are supposed to update your first table where SortYesNo = "Yes"

So, where and how are you getting lost?
0
 
ggodwinAuthor Commented:
I am walking away from that original thought. As mentioned above, I don't want to create a new approach with in the same db. I would just to add a couple queries that I can run with in the same functions.

Based on this code, I think it will update the destination table with records that are not currently in SORT but are in QREVALUE.  Once I get that to work then I need to add a line of code that will only insert those records that are marked as "Yes" in Sort (Yes/No)

CURRENTLY I AM NOT GETTING AN ERROR WHEN I RUN THE CODE. HOWEVER, IT IS NOT INSERTING RECORDS.

INSERT INTO Sort ( QPRQPINumber, TagNumber, NAMC, ScrapRecordTag )
SELECT QREVALUE.[QPR/QPI Number], QREVALUE.[Tag Number], QREVALUE.NAMC, QREVALUE.ScrapRecordTag  AS Expr1
FROM QREVALUE LEFT JOIN Sort ON QREVALUE.[Tag Number]=Sort.TagNumber
WHERE (((QREVALUE.TagNumber) Is Null));

Open in new window

0
 
sammySeltzerCommented:
Then in that case, something like this

INSERT INTO Sort ( QPRQPINumber, TagNumber, NAMC, ScrapRecordTag )

SELECT QREVALUE.[QPR/QPI Number], QREVALUE.[Tag Number], QREVALUE.NAMC, QREVALUE.ScrapRecordTag 
FROM QREVALUE

WHERE  (((QREVALUE.TagNumber) Is Null
  AND QREVALUE.[Tag Number] NOT IN (SELECT DISTINCT TagNumber
                         FROM SORT
                         WHERE QREVALUE.[Tag Number]=Sort.TagNumber))

Open in new window

0
 
ggodwinAuthor Commented:
Sammy,
I had to tweak the syntax for the fields. I never got any errors but also didn't get any records to insert into SORT.

any idea?
0
 
ggodwinAuthor Commented:
INSERT INTO Sort ( QPRQPINumber, TagNumber, NAMC, ScrapRecordTag )
SELECT QREVALUE.QPRQPINumber, QREVALUE.TagNumber, QREVALUE.NAMC, QREVALUE.ScrapRecordTag
FROM QREVALUE
WHERE (((QREVALUE.TagNumber) Is Null
  AND QREVALUE.TagNumber NOT IN (SELECT DISTINCT TagNumber
                         FROM SORT
                         WHERE QREVALUE.TagNumber=Sort.TagNumber)));

Open in new window

0
 
sammySeltzerCommented:
try changing this:

WHERE (((QREVALUE.TagNumber) Is Null

Open in new window


to this:

WHERE (((QREVALUE.TagNumber) Is NOT Null

Open in new window


since you want records that exist in that table.

Infact, you can take that out completely and simply use:

WHERE (((QREVALUE.TagNumber NOT IN (SELECT DISTINCT TagNumber
                         FROM SORT
                         WHERE QREVALUE.TagNumber=Sort.TagNumber)));
0
 
ggodwinAuthor Commented:
Ok that works,

now one last step.

I need to add one last condition.

I only want records inserted that have "Yes" in the SortYesNo field that is located in QREVALUE.

Here is the code that I used.
INSERT INTO Sort ( QPRQPINumber, TagNumber, NAMC, ScrapRecordTag )
SELECT QREVALUE.QPRQPINumber, QREVALUE.TagNumber, QREVALUE.NAMC, QREVALUE.ScrapRecordTag
FROM QREVALUE
WHERE (((QREVALUE.TagNumber) Is not  Null
  AND QREVALUE.TagNumber NOT IN (SELECT DISTINCT TagNumber
                         FROM SORT
                         WHERE QREVALUE.TagNumber=Sort.TagNumber)));

Open in new window

0
 
sammySeltzerCommented:
Easy!

Just put the WHERE clause back; only this time, change it from WHERE TagNumber is null to

WHERE SortYesNo = 'Yes'

INSERT INTO Sort ( QPRQPINumber, TagNumber, NAMC, ScrapRecordTag )
SELECT QREVALUE.QPRQPINumber, QREVALUE.TagNumber, QREVALUE.NAMC, QREVALUE.ScrapRecordTag
FROM QREVALUE
WHERE (((QREVALUE.SortYestNo = 'Yes'
AND QREVALUE.TagNumber) Is not  Null
  AND QREVALUE.TagNumber NOT IN (SELECT DISTINCT TagNumber
                         FROM SORT
                         WHERE QREVALUE.TagNumber=Sort.TagNumber)));

Open in new window

0
 
ggodwinAuthor Commented:
I got the prompt to "Enter Parameter" QREVALUE.SortYesNo
0
 
sammySeltzerCommented:
I thought you said SortYesNo is a fieldname in QREVALUE table?

What that prompt means is that it cannot find SortYesNo in that table.

Verify that it exists in that table and that the name is spelled correctly.
0
 
ggodwinAuthor Commented:
I double checked. Initially, that field was not in the SORT table.
I added it and confirmed that the field is in QREVALUE.

It is now in both and I still have the prompt.

INSERT INTO Sort ( QPRQPINumber, TagNumber, NAMC, ScrapRecordTag, SortYesNo )
SELECT QREVALUE.QPRQPINumber, QREVALUE.TagNumber, QREVALUE.NAMC, QREVALUE.ScrapRecordTag, QREVALUE.SortYesNo
FROM QREVALUE
WHERE (((QREVALUE.SortYestNo = 'Yes'
AND QREVALUE.TagNumber) Is not  Null
  AND QREVALUE.TagNumber NOT IN (SELECT DISTINCT TagNumber
                         FROM SORT
                         WHERE QREVALUE.TagNumber=Sort.TagNumber)));

Open in new window

0
 
sammySeltzerCommented:
Ok, let's change this:

WHERE (((QREVALUE.SortYestNo = 'Yes'

to

WHERE (((QREVALUE.SortYestNo = "Yes"


It may be datatype issue.

Give that a go.
0
 
ggodwinAuthor Commented:
OK,
Still the same thing.

FYI QREVALUE.SortYesNo is populated with a combo box and picking "Yes" in the list. The Data Type is TEXT
0
 
sammySeltzerCommented:
Try this:

INSERT INTO Sort ( QPRQPINumber, TagNumber, NAMC, ScrapRecordTag, SortYesNo )
SELECT QREVALUE.QPRQPINumber, QREVALUE.TagNumber, QREVALUE.NAMC, QREVALUE.ScrapRecordTag, QREVALUE.SortYesNo
FROM QREVALUE
WHERE (((QREVALUE.SortYestNo) = 'Yes'
AND (QREVALUE.TagNumber) Is not  Null
  AND QREVALUE.TagNumber NOT IN (SELECT DISTINCT TagNumber
                         FROM SORT
                         WHERE QREVALUE.TagNumber=Sort.TagNumber));

Open in new window


I hope I didn't misplace the brackets
0
 
ggodwinAuthor Commented:
I think I see the issue.
SortYestNo is supposed to be SortYesNo

there is a "t" in there.

Let me check it....
0
 
ggodwinAuthor Commented:
That was it
0
 
ggodwinAuthor Commented:
Awesome!
0
 
sammySeltzerCommented:
Good catch!

Sometimes, you almost have to be a private eye to see these tiny annoyances.
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.

Join & Write a Comment

Featured Post

The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now