Re: Need subquery

From: Alex Filonov <afilonov_at_yahoo.com>
Date: 30 Jun 2004 10:06:31 -0700
Message-ID: <336da121.0406300906.5af4ef5f@posting.google.com>

girivasan2000_at_yahoo.com (Girivasan) wrote in message news:<33361f2d.0406280515.28803d55_at_posting.google.com>...
> I need a subquery require to copy a content into another table of same
> field names. The table1 having 100 records and table2 having 50
> records
>
> Query is :
>
> update stock1 t1 set t1.amt = (select t2.amt from stock2 t2 where
> t1.pr_no = t2.pr_no)
>
> The amt column is not null constraint,
> If running this query it shows Null value error
>
> What is the reason, explain me.

You don't have WHERE clause on update statment itself, thus it tries to update all rows in t1. And fails for rows which don't have corresponding rows in t2.
Add next WHERE clause to the update statement:

WHERE exists (select 1 from stock2 t3 where t3.pr_no = t1.pr_no) Received on Wed Jun 30 2004 - 12:06:31 CDT