updating a record

[georgeous]

I have a table with records.
Everytime a new record is inserted, 3 of the fields are left NULL - which is fine, cos i get them later from 3 other tables using this code:

select max(file_id) as mf, max(detail_id) as md, max(cat_id) as mc from fil, detail, cat

what i want to do is update a record in my original table to reflect this new data. ( i would like it to read

original table (stuff)
---------------

record 1 mf, md , mc

do i do (update STUFF
set x='mf', y='md', z='mc'
where ........etc)

is this the right syntax?

 

[bperry]

In this situation, I would ordinarily be thinking of something like the following. (untested). You could give it a try, see if it gets you anywhere. You don't say how to select records for updating from OrigTable, so fill in your own Where criteria (if needed) as you see fit.

UPDATE OrigTable SET
mf = dt.mf,
md = dt.md,
mc = dt.mc
from OrigTable
INNER JOIN
(select
'1' as LinkColumn,
max(file_id) as mf,
max(detail_id) as md,
max(cat_id) as mc
from fil, detail, cat
) as dt
ON = dt.LinkColumn = '1'
WHERE putyourownwherecriteriahere
--------------------------------------------

 

[bperry]

pardon my minor typo.
here's a corrected version.
Sorry about that.
------------------------------
UPDATE OrigTable SET
mf = dt.mf,
md = dt.md,
mc = dt.mc
from OrigTable
INNER JOIN
(select
'1' as LinkColumn,
max(file_id) as mf,
max(detail_id) as md,
max(cat_id) as mc
from fil, detail, cat
) as dt
ON dt.LinkColumn = '1'
WHERE putyourownwherecriteriahere
-----------------------------------