Results 1 to 9 of 9

Thread: TSQL problem!

  1. #1
    Join Date
    Jan 2004
    Posts
    26

    Unanswered: TSQL problem!

    there is a table:
    -------------------------
    ID F1 group
    -------------------------
    1 abcdefg -1
    2 aldjflakj -1
    3 end -1
    4 aldjflajk -1
    5 qeuroim -1
    6 end -1
    ......
    then how to update table like that in one TSQL statement or one simple batch without cursor ?
    -------------------------
    ID F1 group
    -------------------------
    1 abcdefg 1
    2 aldjflakj 1
    3 end 1
    4 aldjflajk 2
    5 qeuroim 2
    6 end 2
    ......

    I have no idea about how to wirte the SQL.
    I am doubt of its possibility,but I can't prove it....
    ......

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Are you saying that the data is ordered in the table?

    wont stay that way for long unless you have columns that identify its place in a result set.

    The order of data in a relational database is meaningless..
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  3. #3
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Update YourTable
    set YourTable.group = NewGroups.NewGroup
    from YourTable
    inner join
    (SELECT ID, isnull(sum(SubCount), 0)+1 as NewGroup
    from YourTable
    left outer join (select ID, 1 as SubCount from YourTable where F1 = 'End') GroupEnds on YourTable.ID > GroupEnds.ID
    group by YourTable.ID) NewGroups
    on YourTable.ID = NewGroups.ID
    If it's not practically useful, then it's practically useless.

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

  4. #4
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    spare the rod, spoil the dba....

    they are still going to have difficulty...
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  5. #5
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    I'll bet they are going to have difficulty because they don't have a dba to spoil.

    This could conceivably be part of an import process, where the ID values indicate the order of the raw data.
    If it's not practically useful, then it's practically useless.

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

  6. #6
    Join Date
    Jan 2004
    Posts
    26
    wow...amazing SQL! you taught me new Skill of using Sql.

    thanks to all friends!!
    ......

  7. #7
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Only problem is it's misleading...blind dude?

    Yes?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  8. #8
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Use your new-found knowledge for good, not for evil my son.


    Brett: Misleading? I dunno. It looks suspiciously screwy, but it could be legit.
    If it's not practically useful, then it's practically useless.

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

  9. #9
    Join Date
    Jan 2004
    Posts
    26
    aha, I forgot to explain why I need a such TSql,here is the whole matter:

    I got one .sql file contains thousands of tsql-batches which each batch was end with a 'GO', a table list contains many table names.
    -the .sql file is like :
    create table ..... (
    col1 integer not null
    ,col integer not null
    .......
    )
    go
    create trigger ....
    begin
    ......
    end
    go
    select ....
    go
    ......
    -the table list is like:
    table1
    table2
    ......
    I was going to find out batches which has no relations to any table in the list then delete it.
    as a try, I want to do it by using TSQL as simple as possible,so I:
    -1.import the sql file to line_Table which each row hold one sql line.
    -2.import the list of table names to name_table which each row hold a table name.
    -3.made a batch ID for each row of line_table.
    -4.then I compare line_table to name_table to find row in line_table has nothing to do with any name in name_table and delete the row or other rows has the same batch ID.
    -5.export tables to files as last

    I can use cursor to do the work, but anyway it is really a challenge for me to doing the 3th step without cursor untill blindman give the answer...
    Last edited by cinrain; 03-03-04 at 12:48.
    ......

Posting Permissions

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