[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

Update Query from two tables based on a condition

Posted on 2014-02-11
23
Medium Priority
?
148 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
[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
23 Comments
 
LVL 11

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
Learn how to optimize MySQL for your business need

With the increasing importance of apps & networks in both business & personal interconnections, perfor. has become one of the key metrics of successful communication. This ebook is a hands-on business-case-driven guide to understanding MySQL query parameter tuning & database perf

 
LVL 29

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 29

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 29

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 29

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
 

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 29

Accepted Solution

by:
sammySeltzer earned 1400 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 29

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 29

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 29

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 29

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

Moving data to the cloud? Find out if you’re ready

Before moving to the cloud, it is important to carefully define your db needs, plan for the migration & understand prod. environment. This wp explains how to define what you need from a cloud provider, plan for the migration & what putting a cloud solution into practice entails.

Question has a verified solution.

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

Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …
Want to learn how to record your desktop screen without having to use an outside camera. Click on this video and learn how to use the cool google extension called "Screencastify"! Step 1: Open a new google tab Step 2: Go to the left hand upper corn…

656 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