Need help with an update

From: Håkon Erdal <he_at_hiadata.no>
Date: Thu, 24 Jan 2002 22:49:22 GMT
Message-ID: <Sb048.1484$pd5.36044@news2.ulv.nextra.no>

Hi

I have a database with the following three tables:

TABLE_A

    Id                Number(10)

TABLE_B
    Id                Number(10)
    RegDate      Date

    TableAId Number(10)

TABLE_C

    Id                Number(10)
    RegDate       Date

    TableAId Number(10)
    TableBId Number(10)

Due to historical problems one of our applications have introduced inconsistency in the base and I want to construct an update that reestablishes consistency. I have tried the following but get an error on the subselect:

UPDATE TABLE_C SET TableBId = (

                    SELECT DISTINCT Id FROM TABLE_B B, TABLE_C C WHERE
B.TableAId=C.TableAId AND B.RegDate=C.RegDate)
        WHERE C.TableBId=B.Id AND C.TableAId<>B.TableAId

Any hints??

Regards

Håkon Erdal
Hiadata as
Norway Received on Thu Jan 24 2002 - 16:49:22 CST