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: <f450deeef0b2a3adad4239bad260c855.16981@mygate.mailgate.org>

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

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