Page 1 of 2 12 LastLast
Results 1 to 15 of 24
  1. #1
    Join Date
    Jan 2009
    Posts
    47

    Unanswered: How to update data?

    I have a table like this:

    ID, ESubject, GroupName
    1 Hello NULL
    2 Re: Hello NULL
    3 RE: Hello NULL
    4 Fw: Hello NULL
    5 Hi NULL
    6 Re: Hello NULL
    7 Re: Hi NULL
    8 Testing NULL
    9 Re: Testing NULL


    Now i have to update GroupName. e.g Hello, Re: Hello, RE: Hello are same subject. I want this output:

    ID, ESubject, GroupName
    1 Hello Hello
    2 Re: Hello Hello
    3 RE: Hello Hello
    4 Fw: Hello Hello
    5 Hi Hi
    6 Re: Hello Hello
    7 Re: Hi Hi
    8 Testing Testing
    9 Re: Testing Testing

    Pls anybody can write a script to update GroupName look like above output.
    Last edited by rudba; 01-20-09 at 14:13.

  2. #2
    Join Date
    Nov 2003
    Location
    London
    Posts
    169
    The logic doesn't seem quite right here

    What happens if 2 emails have the same subject name? how will you tell which is which?

  3. #3
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,799
    Provided Answers: 11
    Look up the REPLACE function in Books Online.

  4. #4
    Join Date
    Nov 2003
    Location
    London
    Posts
    169
    Quote Originally Posted by MCrowley
    Look up the REPLACE function in Books Online.
    I don't quite understand this, surely to update a column from NULL within a specific criteria then you need an UPDATE statement. Please explain

  5. #5
    Join Date
    Jan 2009
    Posts
    47
    It will be same. eg. if there are 2 same ESubject it will be same group

    Quote Originally Posted by SQLSlammer
    The logic doesn't seem quite right here

    What happens if 2 emails have the same subject name? how will you tell which is which?

  6. #6
    Join Date
    Nov 2003
    Location
    London
    Posts
    169
    This peice of code should point you in the right direction


    declare @k varchar(20)
    set @k = 're: hello'


    select right(@k,len(@k)-charindex(':',@k)-1)

  7. #7
    Join Date
    Dec 2007
    Location
    London, UK
    Posts
    741
    UPDATE tbl SET GroupName =
    CASE WHEN ESubject LIKE 're: %' OR ESubject LIKE 'fw: %'
    THEN STUFF(ESubject,1,4,'') ELSE ESubject END ;

  8. #8
    Join Date
    Jan 2009
    Posts
    47
    Thanks SQLSlammer and dportas.
    Sorry for my requirment is slidely change.
    I have to update GroupID with first ID (lowest ID). If there are 3 records, all 3 records will be update lowest ID not ESubject.

    Outlooks like this:
    ID ESubject GroupID
    1 Hello 1
    2 Re: Hello 1
    3 RE: Hello 1
    4 Fw: Hello 1
    5 Hi 5
    6 Re: Hello 1
    7 Re: Hi 5
    8 Testing 8
    9 Re: Testing 8
    Last edited by rudba; 01-20-09 at 21:21.

  9. #9
    Join Date
    Dec 2008
    Posts
    135
    try this once
    declare @tab table(ID int, ESubject varchar(32), Groupid int)
    insert into @tab select 1, 'Hello', NULL union all select
    2, 'Re: Hello', NULL union all select
    3, 'RE: Hello', NULL union all select
    4, 'Fw: Hello', NULL union all select
    5, 'Hi', NULL union all select
    6, 'Re: Hello', NULL union all select
    7, 'Re: Hi', NULL union all select
    8, 'Testing' ,NULL union all select
    9, 'Re: Testing', NULL

    update s
    set s.groupid = t.mini
    from @tab s
    inner join
    (select min(s.id)as mini,t.esubject from @tab s inner join @tab t on 1=1
    where t.esubject like '%'+s.esubject+'%' group by t.esubject
    ) t on t.esubject = s.esubject

    select * from @tab
    Last edited by bklr; 01-21-09 at 01:22.

  10. #10
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    He should write a user defined function to contain the logic for stripping and parsing the subject lines.
    Correction: he should HIRE SOMEBODY WHO KNOWS WHAT THEY ARE DOING to write this function, and all the other database code he needs. He is clearly in way over his head.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  11. #11
    Join Date
    Apr 2008
    Location
    Along the shores of Lake Michigan
    Posts
    242
    Quote Originally Posted by rudba
    Sorry for my requirment is slidely change.
    I hate when that happens.......

  12. #12
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,799
    Provided Answers: 11
    Quote Originally Posted by rudba
    Sorry for my requirment is slidely [sic] change.
    Whoa! What are the odds of that happening?

  13. #13
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Sorry our answers were slidely wrong.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  14. #14
    Join Date
    Jan 2009
    Posts
    47
    Thanks
    It is works if we have few data. But in my case, i have too many data so it is taking long time.

    anybody can fix to update fast?

    Quote Originally Posted by bklr
    try this once
    declare @tab table(ID int, ESubject varchar(32), Groupid int)
    insert into @tab select 1, 'Hello', NULL union all select
    2, 'Re: Hello', NULL union all select
    3, 'RE: Hello', NULL union all select
    4, 'Fw: Hello', NULL union all select
    5, 'Hi', NULL union all select
    6, 'Re: Hello', NULL union all select
    7, 'Re: Hi', NULL union all select
    8, 'Testing' ,NULL union all select
    9, 'Re: Testing', NULL

    update s
    set s.groupid = t.mini
    from @tab s
    inner join
    (select min(s.id)as mini,t.esubject from @tab s inner join @tab t on 1=1
    where t.esubject like '%'+s.esubject+'%' group by t.esubject
    ) t on t.esubject = s.esubject

    select * from @tab

  15. #15
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10

    Cool How many points does this get me?

    I think these are pretty reasonable NZDFs...
    Code:
    UPDATE emails
    SET    groupname = Stuff(subject + Coalesce(LTrim(Reverse(Left(Reverse(subject), CharIndex(Reverse(': '), Reverse(subject))))), Coalesce(NullIf(subject, 'NULL'), subject)), 1, Len(subject), '')
    Code:
    UPDATE emails
    SET    groupname = Coalesce(SubString(subject, NullIf(CharIndex(': ', subject) + 200, 200) - 198, (Len(subject) * 2) - (NullIf(CharIndex(': ', subject) + 1, 2)) * 2), LTrim(subject), Convert(nvarchar(1), subject))
    Last edited by gvee; 01-21-09 at 13:18. Reason: added title
    George
    Home | Blog

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •