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.
