One way to do it is to only update T1 when a corresponding record exists on T2:
update t1 set txt1 = ( select txt2 from t2 where t2.id = t1.id ) where exists ( select * from t2 where t2.id = t1.id ) On Tue, Mar 6, 2012 at 12:10 PM, TXVanguard <[email protected]> wrote: > > Do I need to duplicate the WHERE clause used in the SELECT statement? Can > you show me how to rewrite the UPDATE statement? (I'm not looking for help > with homework; I'm a beginner, trying to learn SQL while I'm porting some > software at work.) Thanks. > > > Jean-Yves Linet-2 wrote: > > > > You don't have where clause in your update, so all rows of t1 are > updated. > > And as you have no row in t2 with id=1, the select return a null value. > > > > Le 6 mars 2012 02:19, TXVanguard <[email protected]> a écrit : > > > >> > >> When I run the following SQL statements in JavaDB: > >> > >> ================================================ > >> > >> drop table t1; > >> drop table t2; > >> > >> create table t1 ( TXT1 varchar(6), ID integer ); > >> > >> create table t2 ( ID integer, TXT2 varchar(12)); > >> > >> insert into t1 (TXT1,id) values ('ONE',1); > >> insert into t1 (TXT1,id) values ('TWO',2); > >> > >> insert into t2 (id,TXT2) values (2,'TWO'); > >> insert into t2 (id,TXT2) values (3,'THREE'); > >> > >> UPDATE t1 SET TXT1 = (select (TXT2) FROM t2 WHERE (t2.ID = t1.ID)); > >> > >> ================================================= > >> > >> then, table t1 has the following values: > >> > >> VAL1 ID > >> ROW 1: <NULL> 1 > >> ROW 2: 'TWO' 2 > >> > >> Why is the value of column VAL1 in the first row set to <NULL> by the > >> update > >> statement? > >> -- > >> View this message in context: > >> http://old.nabble.com/Understanding-an-UPDATE-tp33448196p33448196.html > >> Sent from the Apache Derby Users mailing list archive at Nabble.com. > >> > >> > > > > > > -- > View this message in context: > http://old.nabble.com/Understanding-an-UPDATE-tp33448196p33451523.html > Sent from the Apache Derby Users mailing list archive at Nabble.com. > >
