Update statment problem

9 Jun 2006 10:13 AM

Agnes

(1)select * from tmp_gltransaction where tmp_gltransaction.reportid =
'MGR-6530234110'
(2)UPDATE tmp_gltransaction SET acctname = (select acctname from
chartofaccount where chartofaccount.acctcode =
tmp_gltransaction.acctcode and tmp_gltransaction.reportid =
'MGR-6530234110')

For Statment(1), I found that only 108 records.
However, when I process statment(2) ,there are 2999 records, it seems ignore
my condition
[tmp_gltransaction.reportid = 'MGR-6530234110')]

thanks
From Agnes

9 Jun 2006 10:27 AM

Mike Hodgson

That's because there *is* no WHERE clause, at least not on the UPDATE
statement - you've got the WHERE clause on your subquery, which does not
affect the number of rows updated in the outer query. Why bother with
the subquery at all? Why not write it like this (untested):

    UPDATE t
    SET t.acctname = a.acctname
    FROM tmp_gltransaction AS t
        INNER JOIN chartofaccount AS a ON t.acctcode = a.acctcode
    WHERE t.reportid = 'MGR-6530234110'

--
*mike hodgson*
http://sqlnerd.blogspot.com

Agnes wrote:

Show quote

>(1)select * from tmp_gltransaction where tmp_gltransaction.reportid =
>'MGR-6530234110'
> (2)UPDATE tmp_gltransaction SET acctname = (select acctname from
>chartofaccount where chartofaccount.acctcode =
> tmp_gltransaction.acctcode and tmp_gltransaction.reportid =
>'MGR-6530234110')
>
>For Statment(1), I found that only 108 records.
>However, when I process statment(2) ,there are 2999 records, it seems ignore
>my condition
>[tmp_gltransaction.reportid = 'MGR-6530234110')]
>
>
>thanks
>From Agnes
>
>
>
>
>