SQL Question 
Author Message
 SQL Question

I've got three tables:

list(symbol_id, msg)
event(event_id, symbol_id)
event_type(event_id, desc)

There can be more than one event for any symbol.

I'd like to update table list and set msg to the concatenation
of the event descriptions, something like

update list
set    l.msg = l.msg + et.desc
from   list l
     , event e
     , event_type et
where  e.symbol_id = l.symbol_id
  and  et.event_id = e.event_id

This unfortunately does not "do the right thing".

Any suggestions on how to do this efficently would be appreciated.

Thanks,

Michael
--
Michael Peppler         -||-  Data Migrations Inc.

Int. Sybase User Group  -||-   http://www.***.com/



Sun, 27 Aug 2000 03:00:00 GMT
 SQL Question

That I had:

select * from list
        symbol_id       msg    
        1              
        2              

select * from event
        event_id        symbol_id      
        1       1      
        2       1      
        3       2      
        4       2      

select * from event_type;
        event_id        descr  
        1       One    
        2       Two    
        3       Three  
        4       Four    

That I have executed (some changes):

update list l
set    l.msg = l.msg + et.descr
from
     event e
     , event_type et
where  e.symbol_id = l.symbol_id
  and  et.event_id = e.event_id

And, this is result:

select * from list
        symbol_id       msg    
        1       OneTwo  
        2       ThreeFour      

Do you want to receive such result?

Quote:
> -----Original Message-----

> Posted At: Thursday, March 12, 1998 12:42 AM
> Posted To: sybase
> Conversation:      SQL Question
> Subject:   SQL Question

> I've got three tables:

> list(symbol_id, msg)
> event(event_id, symbol_id)
> event_type(event_id, desc)

> There can be more than one event for any symbol.

> I'd like to update table list and set msg to the concatenation
> of the event descriptions, something like

> update list
> set    l.msg = l.msg + et.desc
> from   list l
>      , event e
>      , event_type et
> where  e.symbol_id = l.symbol_id
>   and  et.event_id = e.event_id

> This unfortunately does not "do the right thing".

> Any suggestions on how to do this efficently would be appreciated.

> Thanks,

> Michael
> --
> Michael Peppler         -||-  Data Migrations Inc.

> Int. Sybase User Group  -||-  http://www.isug.com



Mon, 28 Aug 2000 03:00:00 GMT
 SQL Question

Quote:

> That I have executed (some changes):

> update list l
> set    l.msg = l.msg + et.descr
> from
>      event e
>      , event_type et
> where  e.symbol_id = l.symbol_id
>   and  et.event_id = e.event_id

> And, this is result:

> select * from list
>         symbol_id       msg
>         1       OneTwo
>         2       ThreeFour

> Do you want to receive such result?

That's what I want - but that's not what I get if I try your
query - both 11.0.3 and 11.5 gives me a syntax error at the
'update list l' statement.

Michael

- Show quoted text -

Quote:

> > -----Original Message-----

> > Posted At:    Thursday, March 12, 1998 12:42 AM
> > Posted To:    sybase
> > Conversation: SQL Question
> > Subject:      SQL Question

> > I've got three tables:

> > list(symbol_id, msg)
> > event(event_id, symbol_id)
> > event_type(event_id, desc)

> > There can be more than one event for any symbol.

> > I'd like to update table list and set msg to the concatenation
> > of the event descriptions, something like

> > update list
> > set    l.msg = l.msg + et.desc
> > from   list l
> >      , event e
> >      , event_type et
> > where  e.symbol_id = l.symbol_id
> >   and  et.event_id = e.event_id

> > This unfortunately does not "do the right thing".

> > Any suggestions on how to do this efficently would be appreciated.

> > Thanks,

> > Michael
> > --
> > Michael Peppler         -||-  Data Migrations Inc.

> > Int. Sybase User Group  -||-  http://www.isug.com

--
Michael Peppler         -||-  Data Migrations Inc.

Int. Sybase User Group  -||-  http://www.isug.com


Mon, 28 Aug 2000 03:00:00 GMT
 SQL Question

Quote:


> > That I have executed (some changes):

> > update list l
> > set    l.msg = l.msg + et.descr
> > from
> >      event e
> >      , event_type et
> > where  e.symbol_id = l.symbol_id
> >   and  et.event_id = e.event_id

> > And, this is result:

> > select * from list
> >         symbol_id       msg
> >         1       OneTwo
> >         2       ThreeFour

> > Do you want to receive such result?

> That's what I want - but that's not what I get if I try your
> query - both 11.0.3 and 11.5 gives me a syntax error at the
> 'update list l' statement.

        That's right. The update syntax doesn't allow for an
        alias after its target table name. I suspect the
        problem might be in the set line you have (but I
        might be wrong, since I haven't tested it). Try -

        update  list
        set     msg = msg + desc
        from    list l,
                event e,
                event_type et
        where   etc. ...

        Of course, you could try doing this in perl instead ;-)

-am



Tue, 29 Aug 2000 03:00:00 GMT
 SQL Question

Sorry, but I used Sybase SQL Anywhere 5.5 with Watcom SQL syntax.

Quote:
> -----Original Message-----

> Posted At: Thursday, March 12, 1998 10:22 PM
> Posted To: sybase
> Conversation:      SQL Question
> Subject:   Re: SQL Question

> That's what I want - but that's not what I get if I try your
> query - both 11.0.3 and 11.5 gives me a syntax error at the
> 'update list l' statement.

> Michael



Tue, 29 Aug 2000 03:00:00 GMT
 SQL Question

Quote:



> > > That I have executed (some changes):

> > > update list l
> > > set    l.msg = l.msg + et.descr
> > > from
> > >      event e
> > >      , event_type et
> > > where  e.symbol_id = l.symbol_id
> > >   and  et.event_id = e.event_id

> > > And, this is result:

> > > select * from list
> > >         symbol_id       msg
> > >         1       OneTwo
> > >         2       ThreeFour

> > > Do you want to receive such result?

> > That's what I want - but that's not what I get if I try your
> > query - both 11.0.3 and 11.5 gives me a syntax error at the
> > 'update list l' statement.

>         That's right. The update syntax doesn't allow for an
>         alias after its target table name. I suspect the
>         problem might be in the set line you have (but I
>         might be wrong, since I haven't tested it). Try -

>         update  list
>         set     msg = msg + desc
>         from    list l,
>                 event e,
>                 event_type et
>         where   etc. ...

>         Of course, you could try doing this in perl instead ;-)

Problem is this needs to be run in a stored proc.

Right now I've got two nested cursors (yuck) but I may end up
precomputing these values in batch mode overnight and storing them
in a lookup table.

Oh well...

Michael
--
Michael Peppler         -||-  Data Migrations Inc.

Int. Sybase User Group  -||-  http://www.isug.com



Tue, 29 Aug 2000 03:00:00 GMT
 
 [ 6 post ] 

 Relevant Pages 

1. Access question -> SQL question

2. Update question and SQL question

3. Simple SQL Question for the SQL Gurus

4. SQL question (not really sql server programming)

5. SQL Gurus: Time SQL question

6. SQL Server T-SQL Question

7. SQL Question: Best way to pass array data to SQL Server

8. SQL question for SQL Gurus

9. more dynamic SQL-Server SQL question

10. ms-sql sql question, isnull function

11. SQL Q: Everybody loves a good SQL question


 
Powered by phpBB® Forum Software