Update record need self join problem

17 Feb 2006 8:45 PM

Rich

Hello,

My detail table has a subID column and a CompanyName column. For each subID
there needs to be a corresponding CompanyName.

subID CompanName
a-01    JoneCo
a-01    JoneCo
a-01    Null
b-01    ShmoCo
b-01    ShmoCo
b-01    Null

I need to update this table to replace the Null values with the
corresponding CompanyName for the given subID. So I need to select the subID
and CompanyName where CompanyName Is Not Null and then Update the table for
that subID Where the CompanyName Is Null. This is where I am having my
problem:
Here is my PseudoCode

Update tblCo Set CompanyName = (Select CompanyName from (Select subID,
CompanyName From tblCo Where CoompanyName Is Not Null) t3) t2)
Where CompanyName Is Null And subID = t3.SubID

And here is the DDL

Create Table tblCo(subID varchar(5), CompanyName varchar(20))
Insert Into tblCo select 'a-01', 'jonesCo'
Insert Into tblCo select 'a-01', 'jonesCo'
Insert Into tblCo select 'a-01', null
Insert Into tblCo select 'b-01', 'shmoCo'
Insert Into tblCo select 'b-01', 'shmoCo'
Insert Into tblCo select 'b-01', 'null'
Insert Into tblCo select 'c-01', 'hoCo'
Insert Into tblCo select 'c-01', 'hoCo'
Insert Into tblCo select 'c-01', null
Insert Into tblCo select 'd-01', 'loCo'
Insert Into tblCo select 'd-01', 'loCo'
Insert Into tblCo select 'd-01', 'null'

Any assistance appreciated how I can update these nulls with the
corresponding companyName.

Thanks,
Rich

17 Feb 2006 8:56 PM

Mark Williams

Have you tried

UPDATE detail SET CompanyName = t1.CompanyName
FROM
detail d INNER JOIN
(
SELECT DISTINCT subID, CompanyName FROM detail
WHERE CompanyName IS NOT NULL
) t1
ON d.subID = t1.subID

--

Show quote

"Rich" wrote:

> Hello,
>
> My detail table has a subID column and a CompanyName column. For each subID
> there needs to be a corresponding CompanyName.
>
> subID CompanName
> a-01    JoneCo
> a-01    JoneCo
> a-01    Null
> b-01    ShmoCo
> b-01    ShmoCo
> b-01    Null
>
> I need to update this table to replace the Null values with the
> corresponding CompanyName for the given subID. So I need to select the subID
> and CompanyName where CompanyName Is Not Null and then Update the table for
> that subID Where the CompanyName Is Null. This is where I am having my
> problem:
> Here is my PseudoCode
>
> Update tblCo Set CompanyName = (Select CompanyName from (Select subID,
> CompanyName From tblCo Where CoompanyName Is Not Null) t3) t2)
> Where CompanyName Is Null And subID = t3.SubID
>
> And here is the DDL
>
> Create Table tblCo(subID varchar(5), CompanyName varchar(20))
> Insert Into tblCo select 'a-01', 'jonesCo'
> Insert Into tblCo select 'a-01', 'jonesCo'
> Insert Into tblCo select 'a-01', null
> Insert Into tblCo select 'b-01', 'shmoCo'
> Insert Into tblCo select 'b-01', 'shmoCo'
> Insert Into tblCo select 'b-01', 'null'
> Insert Into tblCo select 'c-01', 'hoCo'
> Insert Into tblCo select 'c-01', 'hoCo'
> Insert Into tblCo select 'c-01', null
> Insert Into tblCo select 'd-01', 'loCo'
> Insert Into tblCo select 'd-01', 'loCo'
> Insert Into tblCo select 'd-01', 'null'
>
> Any assistance appreciated how I can update these nulls with the
> corresponding companyName.
>
> Thanks,
> Rich

17 Feb 2006 9:39 PM

Rich

Thanks this code worked very nicely.

Show quote

"Mark Williams" wrote:

> Have you tried
>
> UPDATE detail SET CompanyName = t1.CompanyName
> FROM
> detail d INNER JOIN
> (
> SELECT DISTINCT subID, CompanyName FROM detail
> WHERE CompanyName IS NOT NULL
> ) t1
> ON d.subID = t1.subID
>
> --
>
> "Rich" wrote:
>
> > Hello,
> >
> > My detail table has a subID column and a CompanyName column. For each subID
> > there needs to be a corresponding CompanyName.
> >
> > subID CompanName
> > a-01    JoneCo
> > a-01    JoneCo
> > a-01    Null
> > b-01    ShmoCo
> > b-01    ShmoCo
> > b-01    Null
> >
> > I need to update this table to replace the Null values with the
> > corresponding CompanyName for the given subID. So I need to select the subID
> > and CompanyName where CompanyName Is Not Null and then Update the table for
> > that subID Where the CompanyName Is Null. This is where I am having my
> > problem:
> > Here is my PseudoCode
> >
> > Update tblCo Set CompanyName = (Select CompanyName from (Select subID,
> > CompanyName From tblCo Where CoompanyName Is Not Null) t3) t2)
> > Where CompanyName Is Null And subID = t3.SubID
> >
> > And here is the DDL
> >
> > Create Table tblCo(subID varchar(5), CompanyName varchar(20))
> > Insert Into tblCo select 'a-01', 'jonesCo'
> > Insert Into tblCo select 'a-01', 'jonesCo'
> > Insert Into tblCo select 'a-01', null
> > Insert Into tblCo select 'b-01', 'shmoCo'
> > Insert Into tblCo select 'b-01', 'shmoCo'
> > Insert Into tblCo select 'b-01', 'null'
> > Insert Into tblCo select 'c-01', 'hoCo'
> > Insert Into tblCo select 'c-01', 'hoCo'
> > Insert Into tblCo select 'c-01', null
> > Insert Into tblCo select 'd-01', 'loCo'
> > Insert Into tblCo select 'd-01', 'loCo'
> > Insert Into tblCo select 'd-01', 'null'
> >
> > Any assistance appreciated how I can update these nulls with the
> > corresponding companyName.
> >
> > Thanks,
> > Rich

17 Feb 2006 9:14 PM

Brian Selzer

First of all, tables aren't supposed to have duplicate rows. Assuming that
the real table doesn't, you should probably apply some normalization
principles to your tables to eliminate the redundancy.

Try this (not tested):

UPDATE tblCo
    SET CompanyName =
        (SELECT DISTINCT CompanyName
            FROM tblCo tc
            WHERE tc.subID = tblCo.subID
                AND tc.CompanyName IS NOT NULL)
    WHERE CompanyName IS NULL

Show quote

"Rich" <R***@discussions.microsoft.com> wrote in message
news:B74986E1-7F4D-4A3D-824E-52610C73B04B@microsoft.com...
> Hello,
>
> My detail table has a subID column and a CompanyName column. For each
> subID
> there needs to be a corresponding CompanyName.
>
> subID CompanName
> a-01    JoneCo
> a-01    JoneCo
> a-01    Null
> b-01    ShmoCo
> b-01    ShmoCo
> b-01    Null
>
> I need to update this table to replace the Null values with the
> corresponding CompanyName for the given subID. So I need to select the
> subID
> and CompanyName where CompanyName Is Not Null and then Update the table
> for
> that subID Where the CompanyName Is Null. This is where I am having my
> problem:
> Here is my PseudoCode
>
> Update tblCo Set CompanyName = (Select CompanyName from (Select subID,
> CompanyName From tblCo Where CoompanyName Is Not Null) t3) t2)
> Where CompanyName Is Null And subID = t3.SubID
>
> And here is the DDL
>
> Create Table tblCo(subID varchar(5), CompanyName varchar(20))
> Insert Into tblCo select 'a-01', 'jonesCo'
> Insert Into tblCo select 'a-01', 'jonesCo'
> Insert Into tblCo select 'a-01', null
> Insert Into tblCo select 'b-01', 'shmoCo'
> Insert Into tblCo select 'b-01', 'shmoCo'
> Insert Into tblCo select 'b-01', 'null'
> Insert Into tblCo select 'c-01', 'hoCo'
> Insert Into tblCo select 'c-01', 'hoCo'
> Insert Into tblCo select 'c-01', null
> Insert Into tblCo select 'd-01', 'loCo'
> Insert Into tblCo select 'd-01', 'loCo'
> Insert Into tblCo select 'd-01', 'null'
>
> Any assistance appreciated how I can update these nulls with the
> corresponding companyName.
>
> Thanks,
> Rich

17 Feb 2006 9:46 PM

Rich

yes. I realize that this example is somewhat denormalized. But there are
worse issues with the actual tables that I need to resolve. In the meantime,
this update fix is just easier. Not to pass the buck, but I inherited the
DB/project. It has already been noted that there are denormalization issues
I need to reslove.

Anyway, your code worked very nicely. Thanks for the help.

Show quote

"Brian Selzer" wrote:

> First of all, tables aren't supposed to have duplicate rows. Assuming that
> the real table doesn't, you should probably apply some normalization
> principles to your tables to eliminate the redundancy.
>
> Try this (not tested):
>
> UPDATE tblCo
>     SET CompanyName =
>         (SELECT DISTINCT CompanyName
>             FROM tblCo tc
>             WHERE tc.subID = tblCo.subID
>                 AND tc.CompanyName IS NOT NULL)
>     WHERE CompanyName IS NULL
>
>
> "Rich" <R***@discussions.microsoft.com> wrote in message
> news:B74986E1-7F4D-4A3D-824E-52610C73B04B@microsoft.com...
> > Hello,
> >
> > My detail table has a subID column and a CompanyName column. For each
> > subID
> > there needs to be a corresponding CompanyName.
> >
> > subID CompanName
> > a-01    JoneCo
> > a-01    JoneCo
> > a-01    Null
> > b-01    ShmoCo
> > b-01    ShmoCo
> > b-01    Null
> >
> > I need to update this table to replace the Null values with the
> > corresponding CompanyName for the given subID. So I need to select the
> > subID
> > and CompanyName where CompanyName Is Not Null and then Update the table
> > for
> > that subID Where the CompanyName Is Null. This is where I am having my
> > problem:
> > Here is my PseudoCode
> >
> > Update tblCo Set CompanyName = (Select CompanyName from (Select subID,
> > CompanyName From tblCo Where CoompanyName Is Not Null) t3) t2)
> > Where CompanyName Is Null And subID = t3.SubID
> >
> > And here is the DDL
> >
> > Create Table tblCo(subID varchar(5), CompanyName varchar(20))
> > Insert Into tblCo select 'a-01', 'jonesCo'
> > Insert Into tblCo select 'a-01', 'jonesCo'
> > Insert Into tblCo select 'a-01', null
> > Insert Into tblCo select 'b-01', 'shmoCo'
> > Insert Into tblCo select 'b-01', 'shmoCo'
> > Insert Into tblCo select 'b-01', 'null'
> > Insert Into tblCo select 'c-01', 'hoCo'
> > Insert Into tblCo select 'c-01', 'hoCo'
> > Insert Into tblCo select 'c-01', null
> > Insert Into tblCo select 'd-01', 'loCo'
> > Insert Into tblCo select 'd-01', 'loCo'
> > Insert Into tblCo select 'd-01', 'null'
> >
> > Any assistance appreciated how I can update these nulls with the
> > corresponding companyName.
> >
> > Thanks,
> > Rich
>
>
>

17 Feb 2006 9:48 PM

--CELKO--

Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, data types, etc. in
your schema are. Sample data is also a good idea, along with clear
specifications. It is very hard to debug code when you do not let us
see it.

What you posted has duplicate rows so that it cannot ever be a table BY
DEFINITION. Make "sub_id" into a PRIMARY KEY. But even if we gave rid
of the duplicates, you should have had a NOT NULL constraint on the
"company_name" to avoid this problem.

Your current disaster has no data integrity.

17 Feb 2006 11:15 PM

Rich

The DDL was posted at the beginning of this thread.

Create Table tblCo(subID varchar(5), CompanyName varchar(20))
Insert Into tblCo select 'a-01', 'jonesCo'
Insert Into tblCo select 'a-01', 'jonesCo'
Insert Into tblCo select 'a-01', null
Insert Into tblCo select 'b-01', 'shmoCo'
Insert Into tblCo select 'b-01', 'shmoCo'
Insert Into tblCo select 'b-01', 'null'
Insert Into tblCo select 'c-01', 'hoCo'
Insert Into tblCo select 'c-01', 'hoCo'
Insert Into tblCo select 'c-01', null
Insert Into tblCo select 'd-01', 'loCo'
Insert Into tblCo select 'd-01', 'loCo'
Insert Into tblCo select 'd-01', 'null'

Show quote

"--CELKO--" wrote:

> Please post DDL, so that people do not have to guess what the keys,
> constraints, Declarative Referential Integrity, data types, etc. in
> your schema are. Sample data is also a good idea, along with clear
> specifications. It is very hard to debug code when you do not let us
> see it.
>
> What you posted has duplicate rows so that it cannot ever be a table BY
> DEFINITION. Make "sub_id" into a PRIMARY KEY. But even if we gave rid
> of the duplicates, you should have had a NOT NULL constraint on the
> "company_name" to avoid this problem.
>
> Your current disaster has no data integrity.
>
>

19 Feb 2006 1:39 AM

--CELKO--

My point is that the DDL is wrong. It has no key, no constraints, and
can never had them as it is written now. Most bad DML comes from bad
DDL. So mop the floor and fix the leak.