Help! Two-Part SQL Update

3 Jun 2005 2:15 PM

xenophon

I have a child table with semicolon-delimited data in a single
column. Based on what is in the first part of the semicolon-delimited
data, I need to write a value to a column in the parent table and
then remove that first part of the child-table column. I guess I want
to do a
two-part SQL Update, but am not sure where to begin. The tables
look like this:

Create Table #ParentInfo ( KeyCode VarChar(24) , Status VarChar(64)
)
    Insert Into #ParentInfo( KeyCode )
        Values( 'E1B296FCSYSTEM' )
    Insert Into #ParentInfo( KeyCode )
        Values( '85829EDESYSTEM' )
    Insert Into #ParentInfo( KeyCode )
        Values( 'A5CB9CF5SYSTEM' )
    Insert Into #ParentInfo( KeyCode )
           Values( '4CCF9C15SYSTEM' )
    Insert Into #ParentInfo( KeyCode )
        Values( '40B5DB72SYSTEM' )

Create Table #ChildInfo ( KeyCode VarChar(24) , Comments
VarChar(900) )
    Insert Into #ChildInfo( KeyCode , Comments )
        Values( 'E1B296FCSYSTEM','Demo Import ; InState:NY;Demo
Comments:')
    Insert Into #ChildInfo( KeyCode , Comments )
        Values( '85829EDESYSTEM','Demo Import ; InState:NY;Demo
Comments:')
    Insert Into #ChildInfo( KeyCode , Comments )
        Values( 'A5CB9CF5SYSTEM','Demo Import ; InState:NY;Demo
Comments:')
    Insert Into #ChildInfo( KeyCode , Comments )
        Values( '4CCF9C15SYSTEM','Demo Import ; InState:NY;Demo
Comments:')
    Insert Into #ChildInfo( KeyCode , Comments )
        Values( '40B5DB72SYSTEM','Demo Import ; InState:NY;Demo
Comments:')

So for "E1B296FCSYSTEM" in #ParentInfo, I need to write "Imported"
in the Status column if "Demo Import" is in #ChildInfo.Comments, and
then remove
"Demo Import" from row "E1B296FCSYSTEM" in #ChilInfo.

Thanks.

3 Jun 2005 2:56 PM

oj

something like this should do:

begin tran
update p
set status = case when c.comments like 'Demo Import%' then 'Imported' else
status end
from #ParentInfo p, #ChildInfo c
where p.KeyCode=c.KeyCode
and p.KeyCode='E1B296FCSYSTEM'
if @@error<>0 rollback tran

update #ChildInfo
set comments = replace(comments,'Demo Import','')
where KeyCode='E1B296FCSYSTEM'
if @@error=0 commit tran
else rollback tran

--
-oj

Show quoteHide quote

"xenophon" <xenophon@online.nospam> wrote in message
news:vfp0a1t3mn9r022b5r96h79srejao9ec05@4ax.com...
>
>
> I have a child table with semicolon-delimited data in a single
> column. Based on what is in the first part of the semicolon-delimited
> data, I need to write a value to a column in the parent table and
> then remove that first part of the child-table column. I guess I want
> to do a
> two-part SQL Update, but am not sure where to begin. The tables
> look like this:
>
>
> Create Table #ParentInfo ( KeyCode VarChar(24) , Status VarChar(64)
> )
>    Insert Into #ParentInfo( KeyCode )
>    Values( 'E1B296FCSYSTEM' )
>    Insert Into #ParentInfo( KeyCode )
>    Values( '85829EDESYSTEM' )
>    Insert Into #ParentInfo( KeyCode )
>    Values( 'A5CB9CF5SYSTEM' )
>    Insert Into #ParentInfo( KeyCode )
>       Values( '4CCF9C15SYSTEM' )
>    Insert Into #ParentInfo( KeyCode )
>    Values( '40B5DB72SYSTEM' )
>
> Create Table #ChildInfo ( KeyCode VarChar(24) , Comments
> VarChar(900) )
>    Insert Into #ChildInfo( KeyCode , Comments )
>    Values( 'E1B296FCSYSTEM','Demo Import ; InState:NY;Demo
> Comments:')
>    Insert Into #ChildInfo( KeyCode , Comments )
>    Values( '85829EDESYSTEM','Demo Import ; InState:NY;Demo
> Comments:')
>    Insert Into #ChildInfo( KeyCode , Comments )
>    Values( 'A5CB9CF5SYSTEM','Demo Import ; InState:NY;Demo
> Comments:')
>    Insert Into #ChildInfo( KeyCode , Comments )
>    Values( '4CCF9C15SYSTEM','Demo Import ; InState:NY;Demo
> Comments:')
>    Insert Into #ChildInfo( KeyCode , Comments )
>    Values( '40B5DB72SYSTEM','Demo Import ; InState:NY;Demo
> Comments:')
>
>
> So for "E1B296FCSYSTEM" in #ParentInfo, I need to write "Imported"
> in the Status column if "Demo Import" is in #ChildInfo.Comments, and
> then remove
> "Demo Import" from row "E1B296FCSYSTEM" in #ChilInfo.
>
> Thanks.
>
>
>
>
>

3 Jun 2005 6:59 PM

--CELKO--

I hope those are not the real names. A data element can be a code, but
it is *used* as a key, so key_code is nonsense. You NEVER name a data
element for how it is used in the physical schema; you name it for what
it is in the data model.

ParentInfo is also weird -- singlular so we have only one parent and
are there tables that do not store information?   Likewise, a name like
status does not tell us "status of what?" when we read it.

Try something like this and avoid dangerous proprietary UPDATE ..
FROM.. syntax.

BEGIN
UPDATE Parents
   SET foobar_status
       = 'Imported'
WHERE EXISTS
       (SELECT *
          FROM Children AS C
         WHERE Partents.foobar_id = C.foobar_id
           AND comments LIKE 'Demo Import ; ' + '%');

UPDATE Children
   SET comments = REPLACE(comments, 'Demo Import ; ','');
END;

While we have no DDL or specs, this scares me. You are in violation of
First Normal Form and it look like you are physically moving data from
table to table. That is how we did it with punch cards in the
1950's. You might want to talk to someone with RDBMS design
experience.

9 Jun 2005 1:48 PM

xenophon

1.
These are not real names. All names were changed and stripped
down to protect the guilty. :)

2.
The data was originally a mess, and what I was looking for was
a streamlined way to clean it up - this is a large multi-multi-
step process.

So there it is.

Thanks.

---------------------------

On 3 Jun 2005 11:59:46 -0700, "--CELKO--" <jcelko***@earthlink.net>
wrote:

>I hope those are not the real names. A data element can be a code, but
>it is *used* as a key, so key_code is nonsense. You NEVER name a data
>element for how it is used in the physical schema; you name it for what
>it is in the data model.

[snip]