Results 1 to 3 of 3
  1. #1
    Join Date
    Dec 2005
    Posts
    266

    Unanswered: Hierarchical table (count)

    Hello

    for MS SQL 2000 i am having :

    CREATE TABLE [dbo].[Items](
    [id_Items] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED,
    [id_ItemsSup] [int] NULL,
    [Name] [nvarchar] (100) NOT NULL,
    [SubItems][int] DEFAULT (0)
    ) ON [PRIMARY]



    with :
    UPDATE [Items] SET SubItems = (SELECT COUNT(id_Items) AS ct FROM dbo.Items WHERE id_ItemsSup = 1) WHERE id_Items = 1
    I get how many subItems has Item = 1

    how can I update the Column SubItems (for each row) ?
    to get the total of subItems for each Item ?

    thank you
    Last edited by anselme; 05-03-07 at 03:45.

  2. #2
    Join Date
    Feb 2003
    Location
    India
    Posts
    216
    update Items set subitems = (select count(*) from items where Id_ItemsSup = A.Id_ItemsSup) from Items A

  3. #3
    Join Date
    Dec 2005
    Posts
    266
    it works fine

    thank you

Posting Permissions

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