Re: Probably simple sql question

From: Martin <mjones.satisdata_at_btinternet.com>
Date: Thu, 20 May 1999 19:39:12 +0100
Message-ID: <7i1ko4$8bp$1@uranium.btinternet.com>

Doug,

Your logic is basically correct however you are selecting values from B which match
a value in A - if no record is found that matches on primary key you will have nothing
returned from the sub-query and hence will attempt to see B.offset to NULL.

Try this

   Update B set offset =

                (select offset
                    from A
                 where A.primarykey = B.primarykey)
   WHERE EXISTS
               ( select '1'
                    from A
                  where A.primarykey = B.primarykey)

The where will prevent any B's being updated unless there is a match in A - messy I know.

--
Regards
Martin Jones

---

OCP - DBA
OCP - Application Developer
---------------------------------------------

Doug Cowles <dcowles_at_bigfoot.com> wrote in message news:37445359.7644AB94_at_bigfoot.com...
> I need to do a large update and can't quite figure it out. Essentially,
>
> I have two tables of the same name and fieldtypes in two different
> schemas.
> The only difference is that on one table, a field called "offset" has
> values in
> it and on the other table (table B), it doesn't. The idea is to get
> table b to
> look exactly like table a.
>
> I tried
>
> Update B set offset =
> (select offset from A where primarykey = B.primarykey)
>
> and got
> cannot update mandatory (NOT NULL) column to NULL.
> The offset is not null on both tables, so there are no null values. My
> correlation
> logic must be askew.
>
> Would appreciate any advice,
>
> - Dc.
>
Received on Thu May 20 1999 - 13:39:12 CDT