REPOST: Re: Need help with an update

From: Saikat Chakraborty <saikatchak_at_hotmail.com>
Date: Fri, 25 Jan 2002 10:39:00 +0000 (UTC)
Message-ID: <0$--$%%%_$_$-$_%_$@news.noc.cabal.int>

hi,
Two problems here:

> UPDATE TABLE_C SET TableBId = (
> SELECT DISTINCT Id FROM TABLE_B B, TABLE_C C WHERE

---------------------------------------|

The Id column is ambiguously defined.
> B.TableAId=C.TableAId AND B.RegDate=C.RegDate)
> WHERE C.TableBId=B.Id AND C.TableAId<>B.TableAId

----------------|----------|

Table aliase B,C are not defined in this level.

A possible solution would be

UPDATE table_c c

   SET tablebid = (SELECT DISTINCT b.id

                              FROM table_b b, table_c c
                             WHERE b.tableaid = c.tableaid
                               AND b.regdate = c.regdate)

 WHERE c.id in (
 select c1.id from table_b b1,table_c c1  where c1.tablebid = b1.id AND c1.tableaid <> b1.TABLEAID)

Thanks
Saikat

-- 
Posted via Mailgate.ORG Server - http://www.Mailgate.ORG

========= WAS CANCELLED BY =======:
From: "Saikat Chakraborty" <saikatchak_at_hotmail.com>
Control: cancel <f450deeef0b2a3adad4239bad260c855.16981_at_mygate.mailgate.org>
Subject: cmsg cancel <f450deeef0b2a3adad4239bad260c855.16981_at_mygate.mailgate.org>
Date: Sun, 27 Jan 2002 23:24:25 GMT
Message-ID: <cancel.f450deeef0b2a3adad4239bad260c855.16981_at_mygate.mailgate.org>
X-No-Archive: yes
Newsgroups: microsoft.test,alt.flame.niggers,comp.databases.oracle.server
NNTP-Posting-Host: w088.z064003087.lax-ca.dsl.cnc.net 64.3.87.88
Lines: 1         
Path: news.uni-stuttgart.de!news.fh-hannover.de!feed.news.nacamar.de!news.stealth.net!msrtrans1!msrnewsc1!cppssbbsa01.microsoft.com!tkmsftngp01!tkmsftngp04!u&n&a&c&anceller
Xref: news.uni-stuttgart.de control:40723465

This message was cancelled from within The Unacanceller's glorious new software, Lotus 1-2-3 For Rogue Cancellers.

Received on Fri Jan 25 2002 - 04:39:00 CST