Solved

Update Query from two tables based on a condition

Posted on 2014-02-11
23
140 Views
Last Modified: 2014-02-13
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
Comment
Question by:ggodwin
23 Comments
 
LVL 10

Expert Comment

by:HuaMinChen
ID: 39852241
You can have an Exists check for the condition and then do the update.
0
 
LVL 22

Expert Comment

by:Kelvin Sparks
ID: 39852243
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
 

Author Comment

by:ggodwin
ID: 39852257
Oh I am sorry,
Access 2003
0
 
LVL 28

Expert Comment

by:sammySeltzer
ID: 39852268
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
 

Author Comment

by:ggodwin
ID: 39856722
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
 
LVL 28

Expert Comment

by:sammySeltzer
ID: 39856736
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
 

Author Comment

by:ggodwin
ID: 39856909
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
 
LVL 28

Expert Comment

by:sammySeltzer
ID: 39857013
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
 

Author Comment

by:ggodwin
ID: 39857088
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
 

Author Comment

by:ggodwin
ID: 39857091
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
 
LVL 28

Expert Comment

by:sammySeltzer
ID: 39857169
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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 

Author Comment

by:ggodwin
ID: 39857364
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
 
LVL 28

Accepted Solution

by:
sammySeltzer earned 350 total points
ID: 39857391
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
 

Author Comment

by:ggodwin
ID: 39857471
I got the prompt to "Enter Parameter" QREVALUE.SortYesNo
0
 
LVL 28

Expert Comment

by:sammySeltzer
ID: 39857476
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
 

Author Comment

by:ggodwin
ID: 39857514
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
 
LVL 28

Expert Comment

by:sammySeltzer
ID: 39857547
Ok, let's change this:

WHERE (((QREVALUE.SortYestNo = 'Yes'

to

WHERE (((QREVALUE.SortYestNo = "Yes"


It may be datatype issue.

Give that a go.
0
 

Author Comment

by:ggodwin
ID: 39857575
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
 
LVL 28

Expert Comment

by:sammySeltzer
ID: 39857666
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
 

Author Comment

by:ggodwin
ID: 39857906
I think I see the issue.
SortYestNo is supposed to be SortYesNo

there is a "t" in there.

Let me check it....
0
 

Author Comment

by:ggodwin
ID: 39857912
That was it
0
 

Author Closing Comment

by:ggodwin
ID: 39857913
Awesome!
0
 
LVL 28

Expert Comment

by:sammySeltzer
ID: 39857944
Good catch!

Sometimes, you almost have to be a private eye to see these tiny annoyances.
0

Featured Post

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

As they say in love and is true in SQL: you can sum some Data some of the time, but you can't always aggregate all Data all the time! Introduction: By the end of this Article it is my intention to bring the meaning and value of the above quote to…
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're looking for how to monitor bandwidth using netflow or packet s…

707 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now