Q

Uniquely matching two fields in an SQL table to fields in a different table

Use an UPDATE statement in SQL to match fields in one SQL table on AS/400 to fields in another SQL table.

I am new to SQL and doing SQL by command on the AS/400. I need to make two fields in one table and uniquely match two fields in another table. If I run the following, I get exactly the records I need:

SELECT DISTINCT APPART, APSEQ# FROM METHDO WHERE APODES = 'EXTERNAL DRYING'

the result is

 Part                  Seq 
 Number 
 BLAU1003BA             30 
 BLAU1006AA             30 
 BLAU1004BA             30 
 BLAU1002BA             40 

but when I try to do both tables with this SELECT AOPART, AOSEQ# FROM CSTDAT/METHDR WHERE METHDR.AOPART IN ( SELECT METHDO.APPART FROM METHDO WHERE APO DES = 'EXTERNAL DRYING ') AND
METHDR.AOSEQ# IN (SELECT METHDO.APSEQ# FROM METHDO WHERE APODES = 'EXTERNAL DRYING ') AND AODEPT = '001OS'

I get this:

 Part                  Seq 
 Number 
 BLAU1003BA             30 
 BLAU1006AA             30 
 BLAU1004BA             30 
 BLAU1002BA             30 
 BLAU1002BA             40 

Notice the BLAU1002BA occurs twice. What I believe is happening is that I am getting the part at every possible occurrence of the sequence that my condition exists.

How do I get the unique value and what would be the update statement?

Here's an example of the UPDATE statement that you're trying to run:

UPDATE methdo SET apodes = 
(SELECT R.apodes FROM methdo O INNER JOIN methdr R 
    O.AOPART = R.AOPART and O.AOSEQ# = R.AOSEQ#) 
WHERE EXISTS 
(SELECT 1 FROM methdr R2 
    methdo.AOPART = R2.AOPART and methdo.AOSEQ# = R2.AOSEQ#) 

This was first published in January 2009

Dig deeper on DB2 UDB (universal databases)

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

SearchEnterpriseLinux

SearchDataCenter

Close