RE: SQL UPDATE question

Charles,

That's a great help, thanks a lot.

Actually, if I executed
SELECT T.MyField, count(*)

FROM TransactionFile T
GROUP BY MyField
HAVING COUNT(*) > 1

I'd see only one record for myfield, but looking at the DDS of this file, I see that myfield is one of three keyfields and that the key is unique and so duplicates are technically possible.

This is for a production program. I thought I would go ahead anyway and just check for an SQL error.

-----Message d'origine-----
De : midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] De la part de Charles Wilt
Envoyé : jeudi 16 avril 2009 14:28
À : Midrange Systems Technical Discussion
Objet : Re: SQL UPDATE question

David,

I don't quite understand your question.....

At this point, I would expect you to understand that when
updating a table with the value from another table (an UPDATE
with a "row-fullselect" as the manual puts it) the select
can only return 1 row.

After all, the following wouldn't make sense would it?

update myfile
set myfld = ('Charles','Rob','David')

Obviously, you can't update a single field with a set of values.

So lets look at your statement:
UPDATE MasterFile M
SET FieldToUpdate =
(SELECT T.MyField FROM TransactionFile T WHERE T.KeyField =
M.KeyField )

Assuming KeyField is a unique key in both files and either
-- a record exists in TransactionFile for every Keyfield in MasterFile
-- FieldToUpdate is null capable
Then you're done.

If you don't know if keyfield is unique in Transaction file,
you can always check:
SELECT T.MyField, count(*)
FROM TransactionFile T
GROUP BY MyField
HAVING COUNT(*) > 1

If you get no records, keyfield is unique. (Note for a one
off statement, this is good enough. For a production
statement that will be run over and over, I suggest you had a
unique index over keyField to TransactionFile to ensure that
the pre-condition is enforced)

Conceptually, for every row in MasterFile the following is
run SELECT T.MyField FROM TransactionFile T WHERE T.KeyField
= xxxxxxxx

xxxxxxxx is the value of keyField for that row of MasterFile.

In actual fact, the system tries very hard not to run the
select explicitly for each row of MasterFIle due to
performance reasons. But it will if it has to.

Going on....

UPDATE MasterFile M
SET FieldToUpdate = (SELECT T.MyField FROM TransactionFile T
WHERE T.KeyField = M.KeyField ) WHERE M.Type='R'

Ok now I'm not updating all the rows in MasterFile, only ones
that meet certain criteria. In this case, only rows that
have Type='R'

UPDATE MasterFile M
SET FieldToUpdate = (SELECT T.MyField FROM TransactionFile T
WHERE T.KeyField = M.KeyField ) WHERE M.type= 'R' AND
M.KeyField IN (SELECT T.KeyField FROM TransactionFile T)

Same thing here except that not only does Type have to equal
'R', but keyField must be one of the values found in the list
of values returned by the statement SELECT T.KeyField FROM
TransactionFile T

Why would I want that? I could have various business reasons
of course. But it's likely that for one or more values of
keyField in MasterFile there is not a matching keyField
record in TransactionFile.
Without a matching xxxxxxx record, null is returned by:
SELECT T.MyField FROM TransactionFile T WHERE T.KeyField = xxxxxxxx

Which might be fine if FieldToUpdate is NULL capable. If
not, then you either need use an alternate value (via
COALESCE()) or don't update that particular row in the first
place. One way to do so is with the
WHERE M.KeyField IN (SELECT T.KeyField FROM
TransactionFile T) another option
WHERE exists (select 1 from TransactionFIle T where
T.keyField = M.KeyField)

If it helps, consider the statement for a single row out of
MasterFile where the KeyField value is 'KEY0001'.
UPDATE MasterFile M
SET FieldToUpdate = (SELECT T.MyField FROM TransactionFile T
WHERE T.KeyField = 'KEY00001' ) WHERE M.type= 'R' AND
M.KeyField = 'KEY0001'

HTH,
Charles



On Thu, Apr 16, 2009 at 6:18 AM, David FOXWELL
<David.FOXWELL@xxxxxxxxx> wrote:

Thanks Rob and Charles.

I wonder if someone could explain this UPDATE request to me?

When updating columns of one table from another, I always

have to search for this example, as I can never remember it.
It would certainly help if I could understand the different
stages of the request.

UPDATE MasterFile M

SET FieldToUpdate =
(SELECT T.MyField FROM TransactionFile T WHERE T.KeyField =
M.KeyField )

WHERE M.type= 'R' AND
M.KeyField IN (SELECT T.KeyField FROM TransactionFile T)

I've had problems where the several row are returned and so

the update fails.

Thanks.
--
This is the Midrange Systems Technical Discussion

(MIDRANGE-L) mailing

list To post a message email: MIDRANGE-L@xxxxxxxxxxxx To subscribe,
unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx Before posting,

please take

a moment to review the archives at
http://archive.midrange.com/midrange-l.

--
This is the Midrange Systems Technical Discussion
(MIDRANGE-L) mailing list To post a message email:
MIDRANGE-L@xxxxxxxxxxxx To subscribe, unsubscribe, or change
list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx Before posting,
please take a moment to review the archives at
http://archive.midrange.com/midrange-l.