sub query in sql update

> > > > I am having trouble with sub-queries on an update statement. I keep > getting a message the the sub-query returned more than exactly one row. > As a work around I tried the following: > > select unique TRANS_ACCOUNT from TRANS > where ..... > into temp x;> > update FRS set> FRS_FREEZE = "Y" > where FRS_ACCOUNT = select x.TRANS_ACCOUNT from TRANS; > Try this: UPDATE table1 SET col1=(SELECT col1 FROM table2 WHERE table1.key=table2.key) a) The condition is in the subquery b) you MUST NOT specify the main table in the subquery from clause - even though it IS used there. c) you may get a wierd response '983 rows updated' when in fact it was only 23, but they got updated a couple of times. d) If you indeed have duplicate data in the key field this will not work and you will have to somehow make the table1.key=table2.key join unique. cheers j. _____________________________________________________________________________ Jack Parker | Hewlett Packard, BSMC Boise, Idaho, USA| BEER jparker@hpbs3645.boi.hp.com | It's not just for breakfast anymore. _____________________________________________________________________________ Any opinions expressed herein are my own and not those of my employers. _____________________________________________________________________________