Re: Update Syntax Question

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) } ***** stuff deleted **** } } 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 Tracy, The last where statement makes it kind of impossible since Informix is looking for the only one table, i.e. d_cat. If you remove the last where it will work, BUT .... the correlated subquery nulls the columns where no match is made, if you chose to update all records ("Y,N" prompt). Try creating an update statement from the authority table, i.e. u_cat and then doing the update. You can unload u_cat and awk or vi it to create the update statements with the where state = literal, and county = literal. My tuppence. It works for me. Yours, Nick ********************************* Nick Nobbe, Library of Congress NLS/BPH mail: nnob@loc.gov *********************************