db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From José Ventura <st.ne...@gmail.com>
Subject Re: Understanding an UPDATE
Date Wed, 07 Mar 2012 17:18:18 GMT
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 <brett.denner@lmco.com> 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 <brett.denner@lmco.com> 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.
>
>

Mime
View raw message