RE: Help with a fairly basic SQL (I think)

Tom,

that explains the error "Result of SELECT more than one row.", if there
are multplie printer names are linked to locn; the recors are not unique.

First try to generate a result set with one record for each locn and
printer before you try an update.

With regards,
Carel Teijgeler



*********** REPLY SEPARATOR ***********

On 9-12-2009 at 11:51 Tom wrote:

This updated the field in the proper file, but with the highest "printer"

value from the backup file.

-----Original Message-----

update adp/locnmstrpf set printer=(select max(a.printer) from
tomh/locnmstrpf a, adp/locnmstrpf b where a.locn=b.locn and a.printer<>' '

and b.printer=' ')

might do it for you.

Michael Schutte

I have two files: TOMH/LOCNMSTRPF, ADP/LOCNMSTRPF - both the same layout

and field names (the TOMH file is a backup of the ADP file). Key field for

each is called LOCN. For all records in ADP/LOCNMSTRPF, I want to update

one field ("printer") with data from the corresponding record/field in

TOMH/LOCNMSTRPF. I've tried the following SQL, but I'm getting an error:

update adp/locnmstrpf set printer=(select a.printer from tomh/locnmstrpf

a,

adp/locnmstrpf b where a.locn=b.locn and a.printer<>' ' and b.printer=' ')

The error is: Result of SELECT more than one row.

Well of course it is. I know I can use RPG to do what I want, but I'd

like to know how to accomplish the same thing using only SQL. There is a
way to

do this, right?