Re: Update Syntax Question
Peter Harris (phr@fmsc.com.au) wrote:
: Tracy,
: assuming state and county are your key fields, do this:
: update d_cat set (code1, code2, code3, code4) =
: ((select code1, code2, code3, code4 from u_cat
: where state = d_cat.state
: and county = d_cat.county))
: where state = (select state from u_cat)
: and county = (select county from u_cat);
IMHO, the correct statement is:
update d_cat set (code1, code2, code3, code4) =
((select code1, code2, code3, code4 from u_cat
where state = d_cat.state
and county = d_cat.county))
where state in (select state from u_cat)
and county in (select county from u_cat);
Even better is (especially if you have composite index):
update d_cat set (code1, code2, code3, code4) =
((select code1, code2, code3, code4 from u_cat
where state = d_cat.state
and county = d_cat.county))
where EXISTS (select state from u_cat
where u_cat.state = d_cat.state
and u_cat.county = d_cat.county)
--Zdeno Miklas SOFTEC spol. s r. o.
e-mail: ash@softec.sk Kutuzovova 23
phone : 42-7-273805 ext.128 831 03 Bratislava
fax : 42-7-272537 SLOVAKIA