Re: Update Syntax Question
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);
select * from d_cat;
Regards, Pete Harris
On Mon, 15 May 1995, Tracy Nedd wrote:
} I've got a question for the group that should be fairly easy. I'm
} having trouble getting the syntax correct for the update statement.
} I have two tables, one the master file (d_cat) and one with the data to be
} updated (u_cat). The structure is as follows:
}
} Table D_CAT Table U_CAT
} ------------ -------------
} state - char 2 state - char 2
} county - char 20 county - char 20
} code1 - char 3 code1 - char 3
} ... ...
} code4 - char 3 code4 - char 3
} ... (13 other fields)
}
} What I need is to update every record in D_CAT with
} codes 1 through 4 from U_CAT where the state and
} county in D_CAT match the state and county in U_CAT
} I've tried the statement many ways, but I get a Informix 201
} Syntax error every time. I have put pound signs around the
} character that the query-language says needs to be modified, but
} I don't think the problem resides there. I know that this is
} trivial, but some times when you work with something for so long,
} you forget the little things. Thanks in advance for any help.
}
} Tracy W. Nedd
} National Weather Service - DBA
} vball@skipper.ssmc.noaa.gov
}
} My current version of the statement is:
}
} update d_cat
} set (code1, code2, code3, code4) =
} (select #c#ode1, code2, code3, code4
} from u_cat
} where
} u_cat.state = d_cat.state and
} u_cat.county = d_cat.county )
}
} where d_cat.state = u_cat.state and
} d_cat.county = u_cat.county
}