AS/400 SQL update using inner join

Would someone please help me with this SQL query?

I am trying to update the lead-time field in F4102 with the value that is in the URAB field in F55PRICE. I am joining F4102 and F55PRICE by LITM and MCU.

The statement below returns a message that “Result of Select more than one row”.



Update dvindta/f4102

Set ibltlv= (select prurab from dvindta/f55price inner join dvindta/f4102 on prmcu = ibmcu and prlitm=iblitm)

Where

Exists (select prurab from dvindta/f55price inner join dvindta/f4102 on prlitm = iblitm and prmcu = ibmcu)


Thanks,
Matt
One World Xe, SP23,Update 7, AS/400 v5r3

It looks like there may be duplicates in the F55PRICE file.

Run the following to verify

Select PRMCU, PRLITM, COUNT(*)
FROM F55PRICE
HAVING Count(*) > 1
GROUP BY PRMCU, PRLITM

pfd <[email protected]> wrote: Would someone please help me with this SQL query?

I am trying to update the lead-time field in F4102 with the value that is in the URAB field in F55PRICE. I am joining F4102 and F55PRICE by LITM and MCU.

The statement below returns a message that “Result of Select more than one row”.



Update dvindta/f4102

Set ibltlv= (select prurab from dvindta/f55price inner join dvindta/f4102 on prmcu = ibmcu and prlitm=iblitm)

Where

Exists (select prurab from dvindta/f55price inner join dvindta/f4102 on prlitm = iblitm and prmcu = ibmcu)


Thanks,
Matt
One World Xe, SP23,Update 7, AS/400 v5r3

I am not an expert on SQL, but I have been trying to make this SQL work for years, and as far as I have been able to figure, it doesn't. You are asking to update a field from a list. Metalogically you have two tables with a join, and you wany to update table a with the joined value from table b. BUT, what your statement actually does is pull ALL values from table B (it's a select statement). This is what is meant by more than one row returned.

If anyone does know the trick to making this work, please let us all know. Otherwise, as far as I can tell you need to use procedural code (but I'm not a programmer, I just play one at work).

As a side note, you don't have to define an inner join, it is the default join.

We use sql statements for mass updates all the time and the data selection must be tight enough to return a unique value for each record to be updated. In this case there must be duplicate records in the F55PRICE file as previously suggested.

However be careful as any records prior to the duplicate rows being returned would have been updated. In a simple replacement update that would not be so bad but in an value update such as "set value = (value + (select othervalue from file where ...)" it can be a problem. That is the statement would have to be refined and rerun and the first set of records could be updated again and again, until the statement is tight enough to return a single value and the statement runs to completion. (Yes, this is the voice of experience.)

A good rule of practice is to 1) extract the rows to be updated prior to update to a temp file and 2) also update another field in the record with a specific value, e.g. IBUSER = 'userprf', IBPID = 'SQLUPD', IBUPMJ = 107163. This permits you to find and if necessary either exclude from further updating or on that rare occasion, fix a screw up.

I think what you are trying to do is what I managed to achieve last year after a lot of trial and error...
Hope the example below helps...

update dlibtp/f43090 a set pcurrf='delete'
where exists (select * from dflprddta/f4101 b where a.pclitm = b.imlitm and a.pcitm <> b.imitm)

Tony,

More like this:

UPDATE f4102 a set iblitm=(select imlitm from f4101 b where a.ibitm=
b.imitm) where ibitm in
(SELECT ibitm FROM f4102,f4101 WHERE ibitm=imitm and litm<>imlitm)

In this example I had to set the Long Item Number in the F4102 after the Long Item Number was revised in the Item Master and the update that is submitted, P40821, was cancelled. Since the Short Item Number is unique the select returns one row from the F4101 for each row in the F4102. The where in clause only selects rows in the F4102 where the Long Item Number doesn't match the F4101. Of course I didn't update the other identity fields that I suggested in my prior post, but that would look like:

UPDATE f4102 a set iblitm=(select imlitm from f4101 b where a.ibitm=
b.imitm), IBPID = 'SQLSTMT', IBUSER= 'SMITHG', IBUPMJ= 107163, IBTDAY = 100006 where ibitm in
(SELECT ibitm FROM f4102,f4101 WHERE ibitm=imitm and iblitm<>imlitm)

Thanks Tony.

I got the following statement to work:
update dvindta/f4102 a set ibltlv=(select prurab from
dvindta/f55price b where a.ibmcu=b.prmcu and a.iblitm=b.prlitm)
where exists (select * from dvindta/f55price b where b.prlitm =
a.iblitm and b.prmcu = a.ibmcu)


George - How do I extract the rows to be updated prior to update to a temp file?


Thanks everyone for your inputs.

Thanks,
Matt
One World Xe SP23,Update 7,AS/400, v5r3

Matt,

Well in your case the records to be updated are:

Select * from dvindta/f4102 a
where exists (select * from dvindta/f55price b where b.prlitm =
a.iblitm and b.prmcu = a.ibmcu)

Basically just took out the "set" portion of the statement. Hit F13, then option 1 to change session attributes, tab to select output and set to a '3' for file output. Hit enter, set the file name, library, and member option, as well as descriptive text (documentation you know). Press enter a couple of times to return to statement. Execute select statement that will output your file. Then remember to F13 and reset your session attributes back to display. Then run your update. (Hint: Leave member option to a '1' so that file will always attempt receate and if already exists will warn you prior to recreating the file with the next select statement by accident.)

To use select statement to check on duplicates use the example of the Group By and Having statement grouping on your key fields prior to attempting the update.

If the PRURAB is the same value on all the records that have the same PRLITM and PRMCU, then you have "exact" duplicates and it's not a problem.

So, first, find if there are different values for the PRURAB field in the F55PRICE table for specific combinations of PRLITM and PRMCU.

So, try the following query.....

SELECT a.PRLITM, a.PRMCU, a.PRURAB, b.PRURAB
FROM F55PRICE a
JOIN F55PRICE b
ON b.PRLITM = a.PRLITM
AND b.PRMCU = a.PRMCU
and b.PRURAB < a.PRURAB

If you have records returned then these values are not truly duplicates and you need to determine which PRURAB value you wish to use MAX, MIN, SUM (total of both records), none of the above. If the answer is "None of the Above" or not always the same, then you have a dilema.

If you do NOT have any records returned, then you have "exact" duplicates and can use the value from either record.

Back the table up and then use the following SQL to update the table.....

Update dvindta/f4102

Set ibltlv= (select max(prurab) from dvindta/f55price inner join dvindta/f4102 on prmcu = ibmcu and prlitm=iblitm)

Where

Exists (select prurab from dvindta/f55price inner join dvindta/f4102 on prlitm = iblitm and prmcu = ibmcu)

Matt,

I agree that you should keep running this in select mode to find out what is being duplicated but here is a suggestion since your inner join may be the probem.

Update dvindta/f4102
Set ibltlv= (select prurab from dvindta/f55price
Where prmcu = ibmcu and prlitm=iblitm)
Where Exists (select prurab from dvindta/f55price
Where prlitm = iblitm and prmcu = ibmcu)

Try this way and see if it eliminates the dupes. If not you have to keep looking at the data and find out how it is getting them from that I would think. Good Luck

Maybe this needs to have the file identified too so try this.

Update dvindta/f4102 a
Set a.ibltlv= (select prurab from dvindta/f55price b
Where b.prmcu = a.ibmcu and b.prlitm=a.iblitm)
Where Exists (select prurab from dvindta/f55price c
Where c.prlitm = a.iblitm and c.prmcu = a.ibmcu)

The file identifiers are required I think. Let us know if it works.

I got the following statement to work:

update dvindta/f4102 a set ibltlv=(select prurab from
dvindta/f55price b where a.ibmcu=b.prmcu and a.iblitm=b.prlitm)
where exists (select * from dvindta/f55price b where b.prlitm = a.iblitm and b.prmcu = a.ibmcu)


Thanks everyone for your suggestions.

Thanks,
Matt
One World Xe SP23,Update 7,AS/400, v5r3