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.
midrange.com
