SQL update question

I have 2 tables "ERMSBOX" and "TEMP". ERMSBOX has 2 fields: BOXNUMBER and LOCATION. TEMP has 2 fields: BOXNUM and NEWLOC. I would like to update ERMSBOX.LOCATION to the value in TEMP.NEWLOC where ERMSBOX.BOXNUMBER = TEMP.BOXNUM. I have tried several different statements, including the one below, but they all return errors. update ermsbox set location =(select newloc from temp) where boxnumber in (select temp.boxnum from temp) Any suggestions would be greatly appreciated. Apryle Phillips aphillips@kclh.com

Apryle Phillips wrote: > > I have 2 tables "ERMSBOX" and "TEMP". ERMSBOX has 2 fields: BOXNUMBER and > LOCATION. TEMP has 2 fields: BOXNUM and NEWLOC. I would like to update > ERMSBOX.LOCATION to the value in TEMP.NEWLOC where ERMSBOX.BOXNUMBER = > TEMP.BOXNUM. I have tried several different statements, including the one > below, but they all return errors. > > update ermsbox set location =(select newloc from temp) > where boxnumber in (select temp.boxnum from temp) Try: UPDATE ermsbox SET location = ( SELECT newloc FROM temp WHERE temp.boxnum = ermxbox.boxnumber ); Like most SQL this simply flows from the text description you gave. I believe you could also create a view as a join of the two tables and update the location column of the view from the value of the newloc column of the view. Art S. Kagel

This statement: begin work; UPDATE ermsbox SET baddlloc = ( SELECT aisle FROM temp_basement WHERE temp_basement.box_number = ermxbox.baddlloc); Produced this error: 522: Table (ermxbox) not selected in query. :( Art S. Kagel wrote in message <37A1D722.EB1B4472@bloomberg.net>... >Apryle Phillips wrote: >> >> I have 2 tables "ERMSBOX" and "TEMP". ERMSBOX has 2 fields: BOXNUMBER and >> LOCATION. TEMP has 2 fields: BOXNUM and NEWLOC. I would like to update >> ERMSBOX.LOCATION to the value in TEMP.NEWLOC where ERMSBOX.BOXNUMBER = >> TEMP.BOXNUM. I have tried several different statements, including the one >> below, but they all return errors. >> >> update ermsbox set location =(select newloc from temp) >> where boxnumber in (select temp.boxnum from temp)> >Try: > >UPDATE ermsbox >SET location = ( > SELECT newloc > FROM temp > WHERE temp.boxnum = ermxbox.boxnumber );> >Like most SQL this simply flows from the text description you gave. > >I believe you could also create a view as a join of the two tables and update >the location column of the view from the value of the newloc column of the >view. > >Art S. Kagel

Now that I've fixed the typo :) I get the following message No where clause on Delete or Update, every row in table will be affected It looks like this will do what we need it to thanks! However, I have one question...What will this statement do to the thousands of rows that do not have a corresponding entry in the temp table? I think a want to unload the table before running this as well. :)

Apryle Phillips wrote: > > This statement: > > begin work; > > UPDATE ermsbox > SET baddlloc = ( > SELECT aisle > FROM temp_basement > WHERE temp_basement.box_number = ermxbox.baddlloc);----------------------------------------------^ This was my typo below and you copied it. This should be the same as the outer table name (ermsbox) above. I just verified again that this statement works, without my typo of course. Art S. Kagel > Produced this error: > > 522: Table (ermxbox) not selected in query.> > :( > Art S. Kagel wrote in message <37A1D722.EB1B4472@bloomberg.net>... > >Apryle Phillips wrote: > >> > >> I have 2 tables "ERMSBOX" and "TEMP". ERMSBOX has 2 fields: BOXNUMBER > and > >> LOCATION. TEMP has 2 fields: BOXNUM and NEWLOC. I would like to update > >> ERMSBOX.LOCATION to the value in TEMP.NEWLOC where ERMSBOX.BOXNUMBER = > >> TEMP.BOXNUM. I have tried several different statements, including the > one > >> below, but they all return errors. > >> > >> update ermsbox set location =(select newloc from temp) > >> where boxnumber in (select temp.boxnum from temp)> > > >Try: > > > >UPDATE ermsbox > >SET location = ( > > SELECT newloc > > FROM temp > > WHERE temp.boxnum = ermxbox.boxnumber );> > > >Like most SQL this simply flows from the text description you gave. > > > >I believe you could also create a view as a join of the two tables and > update > >the location column of the view from the value of the newloc column of the > >view. > > > >Art S. Kagel

Apryle Phillips wrote: > > Now that I've fixed the typo :) I get the following message > > No where clause on Delete or Update, every row in table will be affected > > It looks like this will do what we need it to thanks! However, I have one > question...What will this statement do to the thousands of rows that do not > have a corresponding entry in the temp table? I think a want to unload the > table before running this as well. :) They will get NULL for that column. You can prevent that by adding a WHERE clause containing a non-corellated sub-query: UPDATE mytable SET col_1 = (select colval FROM temptbl WHERE temptbl.key_col = mytable.key_col WHERE key_col in (select unique key_col from temptbl); Of course it NEVER hurts to perform an unload before such updates and it is ALWAYS a good idea to perform a similar select to test the logic for updates and deletes. Art S. Kagel