UPDATE/INSERT to make One-to-Many table become One-to-One

Collapse
This topic is closed.
X
X
 
  • Time
  • Show
Clear All
new posts
  • serge

    UPDATE/INSERT to make One-to-Many table become One-to-One

    I have a scenario where two tables are in a One-to-Many relationship
    and I need to move the data from the Many table to the One table so
    that it becomes a One-to-One relationship.

    I need to salvage the records from the many table and without going
    into detail, one of the reasons I can't do the opposite as
    there are records in the ONE table that I need to keep even if they
    don't have any child records in the MANY table.

    Below I created the code to create the sample tables:

    1- tblProducts is the ONE side table
    2- tblProductDetai ls is the MANY side table
    3- tblProductsResu lt is the RESULT I expect to get after running
    some T-SQL code
    4- tblProductCompo nents is another MANY side table to tblProducts
    5- tblProductCompo nentsResult is the RESULT I expect to get...

    Some of the points to consider:
    6- Normally all UniqueID columns are to be IDENTITY. For
    this sample i am entering the UniqueID values myself.
    7- I don't want to create new tables like tblProductsResu lt
    and tblProductCompo nentsResult. I want to update the real tables.
    I have created the tblxxxResult tables only for this post.
    8- The goal is to update the name of the Product by giving it the
    name of the first matching Name from tblProductDetai ls.
    9- If there are more than one entry in tblProductDetai ls for each
    Product, then I need to create new Products inheriting the original
    Product's information including its child records from tblProductCompo nents.

    If you run the code and open the tables it will be much clearer
    to visually see what I want to achieve.

    CREATE DATABASE MyTestDB
    GO
    USE MyTestDB
    GO

    CREATE TABLE [dbo].[tblProducts] (
    [UniqueID] [int] NOT NULL PRIMARY KEY ,
    [Name] [varchar] (80) NULL,
    [TagNo] [int] NULL
    ) ON [PRIMARY]
    GO

    INSERT INTO tblProducts VALUES (1, 'ABC', 55)
    INSERT INTO tblProducts VALUES (2, 'DEF', 66)
    INSERT INTO tblProducts VALUES (3, 'GHI', 77)
    INSERT INTO tblProducts VALUES (4, 'JKL', 88)

    CREATE TABLE [dbo].[tblProductDetai ls] (
    [UniqueID] [int] NOT NULL PRIMARY KEY ,
    [Name] [varchar] (80) NULL,
    [ProductID] int
    ) ON [PRIMARY]
    GO

    INSERT INTO tblProductDetai ls VALUES (1, 'ABC1', 1)
    INSERT INTO tblProductDetai ls VALUES (2, 'DEF', 2)
    INSERT INTO tblProductDetai ls VALUES (3, 'GHI', 3)
    INSERT INTO tblProductDetai ls VALUES (4, 'GHI2', 3)
    INSERT INTO tblProductDetai ls VALUES (5, 'GHI3', 3)
    INSERT INTO tblProductDetai ls VALUES (6, 'JKL2', 4)
    INSERT INTO tblProductDetai ls VALUES (7, 'JKL', 4)
    INSERT INTO tblProductDetai ls VALUES (8, 'JKL3', 4)
    INSERT INTO tblProductDetai ls VALUES (9, 'JKL4', 4)

    CREATE TABLE [dbo].[tblProductCompo nents] (
    [UniqueID] [int] NOT NULL PRIMARY KEY ,
    [ProductID] int,
    [Component] [varchar] (80) NULL
    ) ON [PRIMARY]
    GO

    INSERT INTO tblProductCompo nents VALUES (1, 1, 'ABCa')
    INSERT INTO tblProductCompo nents VALUES (2, 1, 'ABCb')
    INSERT INTO tblProductCompo nents VALUES (3, 1, 'ABCc')
    INSERT INTO tblProductCompo nents VALUES (4, 2, 'DEFa')
    INSERT INTO tblProductCompo nents VALUES (5, 2, 'DEFb')
    INSERT INTO tblProductCompo nents VALUES (6, 2, 'DEFc')
    INSERT INTO tblProductCompo nents VALUES (7, 2, 'DEFd')
    INSERT INTO tblProductCompo nents VALUES (8, 3, 'GHIa')
    INSERT INTO tblProductCompo nents VALUES (9, 4, 'JKLa')
    INSERT INTO tblProductCompo nents VALUES (10, 4, 'JKLb')

    CREATE TABLE [dbo].[tblProductCompo nentsResult] (
    [UniqueID] [int] NOT NULL PRIMARY KEY ,
    [ProductID] int,
    [Component] [varchar] (80) NULL
    ) ON [PRIMARY]
    GO

    INSERT INTO tblProductCompo nentsResult VALUES (1, 1, 'ABCa')
    INSERT INTO tblProductCompo nentsResult VALUES (2, 1, 'ABCb')
    INSERT INTO tblProductCompo nentsResult VALUES (3, 1, 'ABCc')
    INSERT INTO tblProductCompo nentsResult VALUES (4, 2, 'DEFa')
    INSERT INTO tblProductCompo nentsResult VALUES (5, 2, 'DEFb')
    INSERT INTO tblProductCompo nentsResult VALUES (6, 2, 'DEFc')
    INSERT INTO tblProductCompo nentsResult VALUES (7, 2, 'DEFd')
    INSERT INTO tblProductCompo nentsResult VALUES (8, 3, 'GHIa')
    INSERT INTO tblProductCompo nentsResult VALUES (9, 4, 'JKLa')
    INSERT INTO tblProductCompo nentsResult VALUES (10, 4, 'JKLb')
    INSERT INTO tblProductCompo nentsResult VALUES (11, 5, 'GHIa')
    INSERT INTO tblProductCompo nentsResult VALUES (12, 6, 'GHIa')
    INSERT INTO tblProductCompo nentsResult VALUES (13, 7, 'JKLa')
    INSERT INTO tblProductCompo nentsResult VALUES (14, 7, 'JKLb')
    INSERT INTO tblProductCompo nentsResult VALUES (15, 8, 'JKLa')
    INSERT INTO tblProductCompo nentsResult VALUES (16, 8, 'JKLb')
    INSERT INTO tblProductCompo nentsResult VALUES (17, 9, 'JKLa')
    INSERT INTO tblProductCompo nentsResult VALUES (18, 9, 'JKLb')


    CREATE TABLE [dbo].[tblProductsResu lt] (
    [UniqueID] [int] NOT NULL PRIMARY KEY ,
    [Name] [varchar] (80) NULL,
    [TagNo] [int] NULL
    ) ON [PRIMARY]
    GO

    INSERT INTO tblProductsResu lt VALUES (1, 'ABC1', 55)
    INSERT INTO tblProductsResu lt VALUES (2, 'DEF', 66)
    INSERT INTO tblProductsResu lt VALUES (3, 'GHI', 77)
    INSERT INTO tblProductsResu lt VALUES (4, 'JKL', 88)
    INSERT INTO tblProductsResu lt VALUES (5, 'GHI2', 77)
    INSERT INTO tblProductsResu lt VALUES (6, 'GHI3', 77)
    INSERT INTO tblProductsResu lt VALUES (7, 'JKL2', 88)
    INSERT INTO tblProductsResu lt VALUES (8, 'JKL3', 88)
    INSERT INTO tblProductsResu lt VALUES (9, 'JKL4', 88)


    I appreciate your assistance on this.


    Thank you very much


  • John Bell

    #2
    Re: UPDATE/INSERT to make One-to-Many table become One-to-One

    Hi

    You can rename your table tblproducts using sp_rename then use something
    like:

    INSERT INTO [dbo].[tblProducts] ( [UniqueID], [Name], TagId )
    select (SELECT COUNT(*)
    from [dbo].[oldtblProducts] Q
    JOIN [dbo].tblProductDeta ils E ON E.[ProductID] = Q.[UniqueID]
    WHERE D.[ProductID] > E.[ProductID]
    OR ( D.[ProductID] = E.[ProductID] AND D.Name > E.Name ) ) + 1 AS UniqueID,
    D.Name,P.TagNo
    from [dbo].[oldtblProducts] P
    JOIN [dbo].tblProductDeta ils D ON D.[ProductID] = P.[UniqueID]

    John

    "serge" <sergea@nospam. ehmail.com> wrote in message
    news:mNnoe.2185 2$Or5.1207629@w agner.videotron .net...[color=blue]
    >I have a scenario where two tables are in a One-to-Many relationship
    > and I need to move the data from the Many table to the One table so
    > that it becomes a One-to-One relationship.
    >
    > I need to salvage the records from the many table and without going
    > into detail, one of the reasons I can't do the opposite as
    > there are records in the ONE table that I need to keep even if they
    > don't have any child records in the MANY table.
    >
    > Below I created the code to create the sample tables:
    >
    > 1- tblProducts is the ONE side table
    > 2- tblProductDetai ls is the MANY side table
    > 3- tblProductsResu lt is the RESULT I expect to get after running
    > some T-SQL code
    > 4- tblProductCompo nents is another MANY side table to tblProducts
    > 5- tblProductCompo nentsResult is the RESULT I expect to get...
    >
    > Some of the points to consider:
    > 6- Normally all UniqueID columns are to be IDENTITY. For
    > this sample i am entering the UniqueID values myself.
    > 7- I don't want to create new tables like tblProductsResu lt
    > and tblProductCompo nentsResult. I want to update the real tables.
    > I have created the tblxxxResult tables only for this post.
    > 8- The goal is to update the name of the Product by giving it the
    > name of the first matching Name from tblProductDetai ls.
    > 9- If there are more than one entry in tblProductDetai ls for each
    > Product, then I need to create new Products inheriting the original
    > Product's information including its child records from
    > tblProductCompo nents.
    >
    > If you run the code and open the tables it will be much clearer
    > to visually see what I want to achieve.
    >
    > CREATE DATABASE MyTestDB
    > GO
    > USE MyTestDB
    > GO
    >
    > CREATE TABLE [dbo].[tblProducts] (
    > [UniqueID] [int] NOT NULL PRIMARY KEY ,
    > [Name] [varchar] (80) NULL,
    > [TagNo] [int] NULL
    > ) ON [PRIMARY]
    > GO
    >
    > INSERT INTO tblProducts VALUES (1, 'ABC', 55)
    > INSERT INTO tblProducts VALUES (2, 'DEF', 66)
    > INSERT INTO tblProducts VALUES (3, 'GHI', 77)
    > INSERT INTO tblProducts VALUES (4, 'JKL', 88)
    >
    > CREATE TABLE [dbo].[tblProductDetai ls] (
    > [UniqueID] [int] NOT NULL PRIMARY KEY ,
    > [Name] [varchar] (80) NULL,
    > [ProductID] int
    > ) ON [PRIMARY]
    > GO
    >
    > INSERT INTO tblProductDetai ls VALUES (1, 'ABC1', 1)
    > INSERT INTO tblProductDetai ls VALUES (2, 'DEF', 2)
    > INSERT INTO tblProductDetai ls VALUES (3, 'GHI', 3)
    > INSERT INTO tblProductDetai ls VALUES (4, 'GHI2', 3)
    > INSERT INTO tblProductDetai ls VALUES (5, 'GHI3', 3)
    > INSERT INTO tblProductDetai ls VALUES (6, 'JKL2', 4)
    > INSERT INTO tblProductDetai ls VALUES (7, 'JKL', 4)
    > INSERT INTO tblProductDetai ls VALUES (8, 'JKL3', 4)
    > INSERT INTO tblProductDetai ls VALUES (9, 'JKL4', 4)
    >
    > CREATE TABLE [dbo].[tblProductCompo nents] (
    > [UniqueID] [int] NOT NULL PRIMARY KEY ,
    > [ProductID] int,
    > [Component] [varchar] (80) NULL
    > ) ON [PRIMARY]
    > GO
    >
    > INSERT INTO tblProductCompo nents VALUES (1, 1, 'ABCa')
    > INSERT INTO tblProductCompo nents VALUES (2, 1, 'ABCb')
    > INSERT INTO tblProductCompo nents VALUES (3, 1, 'ABCc')
    > INSERT INTO tblProductCompo nents VALUES (4, 2, 'DEFa')
    > INSERT INTO tblProductCompo nents VALUES (5, 2, 'DEFb')
    > INSERT INTO tblProductCompo nents VALUES (6, 2, 'DEFc')
    > INSERT INTO tblProductCompo nents VALUES (7, 2, 'DEFd')
    > INSERT INTO tblProductCompo nents VALUES (8, 3, 'GHIa')
    > INSERT INTO tblProductCompo nents VALUES (9, 4, 'JKLa')
    > INSERT INTO tblProductCompo nents VALUES (10, 4, 'JKLb')
    >
    > CREATE TABLE [dbo].[tblProductCompo nentsResult] (
    > [UniqueID] [int] NOT NULL PRIMARY KEY ,
    > [ProductID] int,
    > [Component] [varchar] (80) NULL
    > ) ON [PRIMARY]
    > GO
    >
    > INSERT INTO tblProductCompo nentsResult VALUES (1, 1, 'ABCa')
    > INSERT INTO tblProductCompo nentsResult VALUES (2, 1, 'ABCb')
    > INSERT INTO tblProductCompo nentsResult VALUES (3, 1, 'ABCc')
    > INSERT INTO tblProductCompo nentsResult VALUES (4, 2, 'DEFa')
    > INSERT INTO tblProductCompo nentsResult VALUES (5, 2, 'DEFb')
    > INSERT INTO tblProductCompo nentsResult VALUES (6, 2, 'DEFc')
    > INSERT INTO tblProductCompo nentsResult VALUES (7, 2, 'DEFd')
    > INSERT INTO tblProductCompo nentsResult VALUES (8, 3, 'GHIa')
    > INSERT INTO tblProductCompo nentsResult VALUES (9, 4, 'JKLa')
    > INSERT INTO tblProductCompo nentsResult VALUES (10, 4, 'JKLb')
    > INSERT INTO tblProductCompo nentsResult VALUES (11, 5, 'GHIa')
    > INSERT INTO tblProductCompo nentsResult VALUES (12, 6, 'GHIa')
    > INSERT INTO tblProductCompo nentsResult VALUES (13, 7, 'JKLa')
    > INSERT INTO tblProductCompo nentsResult VALUES (14, 7, 'JKLb')
    > INSERT INTO tblProductCompo nentsResult VALUES (15, 8, 'JKLa')
    > INSERT INTO tblProductCompo nentsResult VALUES (16, 8, 'JKLb')
    > INSERT INTO tblProductCompo nentsResult VALUES (17, 9, 'JKLa')
    > INSERT INTO tblProductCompo nentsResult VALUES (18, 9, 'JKLb')
    >
    >
    > CREATE TABLE [dbo].[tblProductsResu lt] (
    > [UniqueID] [int] NOT NULL PRIMARY KEY ,
    > [Name] [varchar] (80) NULL,
    > [TagNo] [int] NULL
    > ) ON [PRIMARY]
    > GO
    >
    > INSERT INTO tblProductsResu lt VALUES (1, 'ABC1', 55)
    > INSERT INTO tblProductsResu lt VALUES (2, 'DEF', 66)
    > INSERT INTO tblProductsResu lt VALUES (3, 'GHI', 77)
    > INSERT INTO tblProductsResu lt VALUES (4, 'JKL', 88)
    > INSERT INTO tblProductsResu lt VALUES (5, 'GHI2', 77)
    > INSERT INTO tblProductsResu lt VALUES (6, 'GHI3', 77)
    > INSERT INTO tblProductsResu lt VALUES (7, 'JKL2', 88)
    > INSERT INTO tblProductsResu lt VALUES (8, 'JKL3', 88)
    > INSERT INTO tblProductsResu lt VALUES (9, 'JKL4', 88)
    >
    >
    > I appreciate your assistance on this.
    >
    >
    > Thank you very much
    >
    >[/color]


    Comment

    • serge

      #3
      Re: UPDATE/INSERT to make One-to-Many table become One-to-One

      Hi John,

      Thanks for the code.

      I ran your code and even though the code looks very nice (a single Insert
      statement), it doesn't work in my case. If you compare my tblProductsResu lt
      and the result generated by your code, the UniqueIDs are values that I can't
      modify for the already existing records in tblProducts because they are
      referenced
      in other tables. My tblProducts UniqueID 4 is "JKL", after running your code
      the
      UniqueID 4 is "GHI2" which would cause problems for me.

      I also completely forgot to mention a bigger problem in my original post.
      I would also need to update the ProductID values in tblProductDetai ls for
      all
      the records that are being created in tblProducts. So everytime a new record
      is created in tblProducts I will need to get the new Identity value of
      tblProducts
      and update the ProductID in tblProductDetai ls.

      I also just noticed the code I had originally posted doesn't have the
      product
      'MNO' for my sample example.

      Here's the whole new code again if anyone is interested to help me out.
      I thought there could be a way to do this using a few update and insert
      statements.
      I suspect now that maybe it is possible but much harder to write than to
      write
      some type of a looping through the records one by one cursor and do
      UPDATE or INSERT statements one record at a time.

      Here's the current code I have for the sample records:

      CREATE DATABASE MyTestDB
      GO
      USE MyTestDB
      GO

      CREATE TABLE [dbo].[tblProducts] (
      [UniqueID] [int] NOT NULL PRIMARY KEY ,
      [Name] [varchar] (80) NULL,
      [TagNo] [int] NULL
      ) ON [PRIMARY]
      GO

      INSERT INTO tblProducts VALUES (1, 'ABC', 55)
      INSERT INTO tblProducts VALUES (2, 'DEF', 66)
      INSERT INTO tblProducts VALUES (3, 'GHI', 77)
      INSERT INTO tblProducts VALUES (4, 'JKL', 88)
      INSERT INTO tblProducts VALUES (5, 'MNO', 99)

      CREATE TABLE [dbo].[tblProductDetai ls] (
      [UniqueID] [int] NOT NULL PRIMARY KEY ,
      [Name] [varchar] (80) NULL,
      [ProductID] int
      ) ON [PRIMARY]
      GO

      INSERT INTO tblProductDetai ls VALUES (1, 'ABC1', 1)
      INSERT INTO tblProductDetai ls VALUES (2, 'DEF', 2)
      INSERT INTO tblProductDetai ls VALUES (3, 'GHI', 3)
      INSERT INTO tblProductDetai ls VALUES (4, 'GHI2', 3)
      INSERT INTO tblProductDetai ls VALUES (5, 'GHI3', 3)
      INSERT INTO tblProductDetai ls VALUES (6, 'JKL2', 4)
      INSERT INTO tblProductDetai ls VALUES (7, 'JKL', 4)
      INSERT INTO tblProductDetai ls VALUES (8, 'JKL3', 4)
      INSERT INTO tblProductDetai ls VALUES (9, 'JKL4', 4)

      CREATE TABLE [dbo].[tblProductCompo nents] (
      [UniqueID] [int] NOT NULL PRIMARY KEY ,
      [ProductID] int,
      [Component] [varchar] (80) NULL
      ) ON [PRIMARY]
      GO

      INSERT INTO tblProductCompo nents VALUES (1, 1, 'ABCa')
      INSERT INTO tblProductCompo nents VALUES (2, 1, 'ABCb')
      INSERT INTO tblProductCompo nents VALUES (3, 1, 'ABCc')
      INSERT INTO tblProductCompo nents VALUES (4, 2, 'DEFa')
      INSERT INTO tblProductCompo nents VALUES (5, 2, 'DEFb')
      INSERT INTO tblProductCompo nents VALUES (6, 2, 'DEFc')
      INSERT INTO tblProductCompo nents VALUES (7, 2, 'DEFd')
      INSERT INTO tblProductCompo nents VALUES (8, 3, 'GHIa')
      INSERT INTO tblProductCompo nents VALUES (9, 4, 'JKLa')
      INSERT INTO tblProductCompo nents VALUES (10, 4, 'JKLb')

      CREATE TABLE [dbo].[tblProductCompo nentsResult] (
      [UniqueID] [int] NOT NULL PRIMARY KEY ,
      [ProductID] int,
      [Component] [varchar] (80) NULL
      ) ON [PRIMARY]
      GO

      INSERT INTO tblProductCompo nentsResult VALUES (1, 1, 'ABCa')
      INSERT INTO tblProductCompo nentsResult VALUES (2, 1, 'ABCb')
      INSERT INTO tblProductCompo nentsResult VALUES (3, 1, 'ABCc')
      INSERT INTO tblProductCompo nentsResult VALUES (4, 2, 'DEFa')
      INSERT INTO tblProductCompo nentsResult VALUES (5, 2, 'DEFb')
      INSERT INTO tblProductCompo nentsResult VALUES (6, 2, 'DEFc')
      INSERT INTO tblProductCompo nentsResult VALUES (7, 2, 'DEFd')
      INSERT INTO tblProductCompo nentsResult VALUES (8, 3, 'GHIa')
      INSERT INTO tblProductCompo nentsResult VALUES (9, 4, 'JKLa')
      INSERT INTO tblProductCompo nentsResult VALUES (10, 4, 'JKLb')
      INSERT INTO tblProductCompo nentsResult VALUES (11, 6, 'GHIa')
      INSERT INTO tblProductCompo nentsResult VALUES (12, 7, 'GHIa')
      INSERT INTO tblProductCompo nentsResult VALUES (13, 8, 'JKLa')
      INSERT INTO tblProductCompo nentsResult VALUES (14, 8, 'JKLb')
      INSERT INTO tblProductCompo nentsResult VALUES (15, 9, 'JKLa')
      INSERT INTO tblProductCompo nentsResult VALUES (16, 9, 'JKLb')
      INSERT INTO tblProductCompo nentsResult VALUES (17, 10, 'JKLa')
      INSERT INTO tblProductCompo nentsResult VALUES (18, 10, 'JKLb')


      CREATE TABLE [dbo].[tblProductsResu lt] (
      [UniqueID] [int] NOT NULL PRIMARY KEY ,
      [Name] [varchar] (80) NULL,
      [TagNo] [int] NULL
      ) ON [PRIMARY]
      GO

      INSERT INTO tblProductsResu lt VALUES (1, 'ABC1', 55)
      INSERT INTO tblProductsResu lt VALUES (2, 'DEF', 66)
      INSERT INTO tblProductsResu lt VALUES (3, 'GHI', 77)
      INSERT INTO tblProductsResu lt VALUES (4, 'JKL', 88)
      INSERT INTO tblProductsResu lt VALUES (5, 'MNO', 99)
      INSERT INTO tblProductsResu lt VALUES (6, 'GHI2', 77)
      INSERT INTO tblProductsResu lt VALUES (7, 'GHI3', 77)
      INSERT INTO tblProductsResu lt VALUES (8, 'JKL2', 88)
      INSERT INTO tblProductsResu lt VALUES (9, 'JKL3', 88)
      INSERT INTO tblProductsResu lt VALUES (10, 'JKL4', 88)


      CREATE TABLE [dbo].[tblProductDetai lsResult] (
      [UniqueID] [int] NOT NULL PRIMARY KEY ,
      [Name] [varchar] (80) NULL,
      [ProductID] int
      ) ON [PRIMARY]
      GO

      INSERT INTO tblProductDetai lsResult VALUES (1, 'ABC1', 1)
      INSERT INTO tblProductDetai lsResult VALUES (2, 'DEF', 2)
      INSERT INTO tblProductDetai lsResult VALUES (3, 'GHI', 3)
      INSERT INTO tblProductDetai lsResult VALUES (4, 'GHI2', 6)
      INSERT INTO tblProductDetai lsResult VALUES (5, 'GHI3', 7)
      INSERT INTO tblProductDetai lsResult VALUES (6, 'JKL2', 8)
      INSERT INTO tblProductDetai lsResult VALUES (7, 'JKL', 4)
      INSERT INTO tblProductDetai lsResult VALUES (8, 'JKL3', 9)
      INSERT INTO tblProductDetai lsResult VALUES (9, 'JKL4', 10)


      Thanks again
      [color=blue]
      > You can rename your table tblproducts using sp_rename then use something
      > like:
      >
      > INSERT INTO [dbo].[tblProducts] ( [UniqueID], [Name], TagId )
      > select (SELECT COUNT(*)
      > from [dbo].[oldtblProducts] Q
      > JOIN [dbo].tblProductDeta ils E ON E.[ProductID] = Q.[UniqueID]
      > WHERE D.[ProductID] > E.[ProductID]
      > OR ( D.[ProductID] = E.[ProductID] AND D.Name > E.Name ) ) + 1 AS[/color]
      UniqueID,[color=blue]
      > D.Name,P.TagNo
      > from [dbo].[oldtblProducts] P
      > JOIN [dbo].tblProductDeta ils D ON D.[ProductID] = P.[UniqueID][/color]



      Comment

      • --CELKO--

        #4
        Re: UPDATE/INSERT to make One-to-Many table become One-to-One

        You have NULL-able columns for everything, no DRI to enforce the
        1-to-many relationship you say is there and no relational keys
        (IDENTITY is an exposed physical locator and cannot be a key by
        definition). Why do keep saying "records", when a table has rows,
        which are completely different things?
        [color=blue][color=green]
        >> The goal is to update the name of the Product by giving it the name of the first matching Name from ProductDetails <<[/color][/color]

        How do you define this matching? Since tables have no ordering what
        does "first" mean? MIN()? That coudl be done with a UPDATE if you had
        real keys.

        Doesn't your industry have a standard part number system? Doesn't your
        company use it or have one of their own? The classic schema design
        for this problem usually looks more like this:

        CREATE TABLE Products
        (product_id INTEGER NOT NULL PRIMARY KEY, -- industry std?
        product_name VARCHAR (80) NOT NULL, -- really that long?
        tag_nbr INTEGER NOT NULL); -- no constraints?

        CREATE TABLE ProductDetails
        (product_id INTEGER NOT NULL
        REFERENCES Products (product_id)
        ON UPDATE CASCADE
        ON DELETE CASCADE,
        detail_name VARCHAR(80) NOT NULL,
        PRIMARY KEY (product_id, detail_name));

        CREATE TABLE ProductComponen ts
        (product_id INTEGER NOT NULL
        REFERENCES Products (product_id)
        ON UPDATE CASCADE
        ON DELETE CASCADE,
        component_name VARCHAR(80) NOT NULL,
        PRIMARY KEY (product_id, component_name) );

        Begin by loading Products and get rid of the IDENTITY column, of
        course. Then load the other tables that reference it.

        Comment

        • serge

          #5
          Re: UPDATE/INSERT to make One-to-Many table become One-to-One

          I am currently working on upgrading a production database
          from an older version to a new version. I inherited the processes
          of how the upgrade is done currently. In this case this part a manual
          "record cleanup" is the process and I need to fix the records one by
          one so I wanted to find a code-based upgrade method instead.

          The tables in this case are sample tables i created to try to match
          the real tables I am dealing with. I can't make any changes to any of the
          database structure. I only need to "clean" it up and prepare for the upgrade
          scripts that are already written previously.

          So the logical questions of why the tables are like this is not something
          I can do to change/update or correct.

          You gave me an idea that i didn't think of. Maybe I can use
          UPDATE CASCADE on the tables to do some of the update work.

          Thanks


          "--CELKO--" <jcelko212@eart hlink.net> wrote in message
          news:1117930866 .608318.77020@o 13g2000cwo.goog legroups.com...[color=blue]
          > You have NULL-able columns for everything, no DRI to enforce the
          > 1-to-many relationship you say is there and no relational keys
          > (IDENTITY is an exposed physical locator and cannot be a key by
          > definition). Why do keep saying "records", when a table has rows,
          > which are completely different things?
          >[color=green][color=darkred]
          > >> The goal is to update the name of the Product by giving it the name of[/color][/color][/color]
          the first matching Name from ProductDetails <<[color=blue]
          >
          > How do you define this matching? Since tables have no ordering what
          > does "first" mean? MIN()? That coudl be done with a UPDATE if you had
          > real keys.
          >
          > Doesn't your industry have a standard part number system? Doesn't your
          > company use it or have one of their own? The classic schema design
          > for this problem usually looks more like this:
          >
          > CREATE TABLE Products
          > (product_id INTEGER NOT NULL PRIMARY KEY, -- industry std?
          > product_name VARCHAR (80) NOT NULL, -- really that long?
          > tag_nbr INTEGER NOT NULL); -- no constraints?
          >
          > CREATE TABLE ProductDetails
          > (product_id INTEGER NOT NULL
          > REFERENCES Products (product_id)
          > ON UPDATE CASCADE
          > ON DELETE CASCADE,
          > detail_name VARCHAR(80) NOT NULL,
          > PRIMARY KEY (product_id, detail_name));
          >
          > CREATE TABLE ProductComponen ts
          > (product_id INTEGER NOT NULL
          > REFERENCES Products (product_id)
          > ON UPDATE CASCADE
          > ON DELETE CASCADE,
          > component_name VARCHAR(80) NOT NULL,
          > PRIMARY KEY (product_id, component_name) );
          >
          > Begin by loading Products and get rid of the IDENTITY column, of
          > course. Then load the other tables that reference it.[/color]



          Comment

          • John Bell

            #6
            Re: UPDATE/INSERT to make One-to-Many table become One-to-One

            Hi

            It sounds like the easiest way to do this is to drop the FKs (ALTER TABLE
            statement) or make them CASCADING. Then have a table of old keys and new
            keys and issue an update statement to change the values. You can then insert
            the new values that do not already exist.

            John


            "serge" <sergea@nospam. ehmail.com> wrote in message
            news:N5qoe.2192 3$Or5.1243216@w agner.videotron .net...[color=blue]
            > Hi John,
            >
            > Thanks for the code.
            >
            > I ran your code and even though the code looks very nice (a single Insert
            > statement), it doesn't work in my case. If you compare my
            > tblProductsResu lt
            > and the result generated by your code, the UniqueIDs are values that I
            > can't
            > modify for the already existing records in tblProducts because they are
            > referenced
            > in other tables. My tblProducts UniqueID 4 is "JKL", after running your
            > code
            > the
            > UniqueID 4 is "GHI2" which would cause problems for me.
            >
            > I also completely forgot to mention a bigger problem in my original post.
            > I would also need to update the ProductID values in tblProductDetai ls for
            > all
            > the records that are being created in tblProducts. So everytime a new
            > record
            > is created in tblProducts I will need to get the new Identity value of
            > tblProducts
            > and update the ProductID in tblProductDetai ls.
            >
            > I also just noticed the code I had originally posted doesn't have the
            > product
            > 'MNO' for my sample example.
            >
            > Here's the whole new code again if anyone is interested to help me out.
            > I thought there could be a way to do this using a few update and insert
            > statements.
            > I suspect now that maybe it is possible but much harder to write than to
            > write
            > some type of a looping through the records one by one cursor and do
            > UPDATE or INSERT statements one record at a time.
            >
            > Here's the current code I have for the sample records:
            >
            > CREATE DATABASE MyTestDB
            > GO
            > USE MyTestDB
            > GO
            >
            > CREATE TABLE [dbo].[tblProducts] (
            > [UniqueID] [int] NOT NULL PRIMARY KEY ,
            > [Name] [varchar] (80) NULL,
            > [TagNo] [int] NULL
            > ) ON [PRIMARY]
            > GO
            >
            > INSERT INTO tblProducts VALUES (1, 'ABC', 55)
            > INSERT INTO tblProducts VALUES (2, 'DEF', 66)
            > INSERT INTO tblProducts VALUES (3, 'GHI', 77)
            > INSERT INTO tblProducts VALUES (4, 'JKL', 88)
            > INSERT INTO tblProducts VALUES (5, 'MNO', 99)
            >
            > CREATE TABLE [dbo].[tblProductDetai ls] (
            > [UniqueID] [int] NOT NULL PRIMARY KEY ,
            > [Name] [varchar] (80) NULL,
            > [ProductID] int
            > ) ON [PRIMARY]
            > GO
            >
            > INSERT INTO tblProductDetai ls VALUES (1, 'ABC1', 1)
            > INSERT INTO tblProductDetai ls VALUES (2, 'DEF', 2)
            > INSERT INTO tblProductDetai ls VALUES (3, 'GHI', 3)
            > INSERT INTO tblProductDetai ls VALUES (4, 'GHI2', 3)
            > INSERT INTO tblProductDetai ls VALUES (5, 'GHI3', 3)
            > INSERT INTO tblProductDetai ls VALUES (6, 'JKL2', 4)
            > INSERT INTO tblProductDetai ls VALUES (7, 'JKL', 4)
            > INSERT INTO tblProductDetai ls VALUES (8, 'JKL3', 4)
            > INSERT INTO tblProductDetai ls VALUES (9, 'JKL4', 4)
            >
            > CREATE TABLE [dbo].[tblProductCompo nents] (
            > [UniqueID] [int] NOT NULL PRIMARY KEY ,
            > [ProductID] int,
            > [Component] [varchar] (80) NULL
            > ) ON [PRIMARY]
            > GO
            >
            > INSERT INTO tblProductCompo nents VALUES (1, 1, 'ABCa')
            > INSERT INTO tblProductCompo nents VALUES (2, 1, 'ABCb')
            > INSERT INTO tblProductCompo nents VALUES (3, 1, 'ABCc')
            > INSERT INTO tblProductCompo nents VALUES (4, 2, 'DEFa')
            > INSERT INTO tblProductCompo nents VALUES (5, 2, 'DEFb')
            > INSERT INTO tblProductCompo nents VALUES (6, 2, 'DEFc')
            > INSERT INTO tblProductCompo nents VALUES (7, 2, 'DEFd')
            > INSERT INTO tblProductCompo nents VALUES (8, 3, 'GHIa')
            > INSERT INTO tblProductCompo nents VALUES (9, 4, 'JKLa')
            > INSERT INTO tblProductCompo nents VALUES (10, 4, 'JKLb')
            >
            > CREATE TABLE [dbo].[tblProductCompo nentsResult] (
            > [UniqueID] [int] NOT NULL PRIMARY KEY ,
            > [ProductID] int,
            > [Component] [varchar] (80) NULL
            > ) ON [PRIMARY]
            > GO
            >
            > INSERT INTO tblProductCompo nentsResult VALUES (1, 1, 'ABCa')
            > INSERT INTO tblProductCompo nentsResult VALUES (2, 1, 'ABCb')
            > INSERT INTO tblProductCompo nentsResult VALUES (3, 1, 'ABCc')
            > INSERT INTO tblProductCompo nentsResult VALUES (4, 2, 'DEFa')
            > INSERT INTO tblProductCompo nentsResult VALUES (5, 2, 'DEFb')
            > INSERT INTO tblProductCompo nentsResult VALUES (6, 2, 'DEFc')
            > INSERT INTO tblProductCompo nentsResult VALUES (7, 2, 'DEFd')
            > INSERT INTO tblProductCompo nentsResult VALUES (8, 3, 'GHIa')
            > INSERT INTO tblProductCompo nentsResult VALUES (9, 4, 'JKLa')
            > INSERT INTO tblProductCompo nentsResult VALUES (10, 4, 'JKLb')
            > INSERT INTO tblProductCompo nentsResult VALUES (11, 6, 'GHIa')
            > INSERT INTO tblProductCompo nentsResult VALUES (12, 7, 'GHIa')
            > INSERT INTO tblProductCompo nentsResult VALUES (13, 8, 'JKLa')
            > INSERT INTO tblProductCompo nentsResult VALUES (14, 8, 'JKLb')
            > INSERT INTO tblProductCompo nentsResult VALUES (15, 9, 'JKLa')
            > INSERT INTO tblProductCompo nentsResult VALUES (16, 9, 'JKLb')
            > INSERT INTO tblProductCompo nentsResult VALUES (17, 10, 'JKLa')
            > INSERT INTO tblProductCompo nentsResult VALUES (18, 10, 'JKLb')
            >
            >
            > CREATE TABLE [dbo].[tblProductsResu lt] (
            > [UniqueID] [int] NOT NULL PRIMARY KEY ,
            > [Name] [varchar] (80) NULL,
            > [TagNo] [int] NULL
            > ) ON [PRIMARY]
            > GO
            >
            > INSERT INTO tblProductsResu lt VALUES (1, 'ABC1', 55)
            > INSERT INTO tblProductsResu lt VALUES (2, 'DEF', 66)
            > INSERT INTO tblProductsResu lt VALUES (3, 'GHI', 77)
            > INSERT INTO tblProductsResu lt VALUES (4, 'JKL', 88)
            > INSERT INTO tblProductsResu lt VALUES (5, 'MNO', 99)
            > INSERT INTO tblProductsResu lt VALUES (6, 'GHI2', 77)
            > INSERT INTO tblProductsResu lt VALUES (7, 'GHI3', 77)
            > INSERT INTO tblProductsResu lt VALUES (8, 'JKL2', 88)
            > INSERT INTO tblProductsResu lt VALUES (9, 'JKL3', 88)
            > INSERT INTO tblProductsResu lt VALUES (10, 'JKL4', 88)
            >
            >
            > CREATE TABLE [dbo].[tblProductDetai lsResult] (
            > [UniqueID] [int] NOT NULL PRIMARY KEY ,
            > [Name] [varchar] (80) NULL,
            > [ProductID] int
            > ) ON [PRIMARY]
            > GO
            >
            > INSERT INTO tblProductDetai lsResult VALUES (1, 'ABC1', 1)
            > INSERT INTO tblProductDetai lsResult VALUES (2, 'DEF', 2)
            > INSERT INTO tblProductDetai lsResult VALUES (3, 'GHI', 3)
            > INSERT INTO tblProductDetai lsResult VALUES (4, 'GHI2', 6)
            > INSERT INTO tblProductDetai lsResult VALUES (5, 'GHI3', 7)
            > INSERT INTO tblProductDetai lsResult VALUES (6, 'JKL2', 8)
            > INSERT INTO tblProductDetai lsResult VALUES (7, 'JKL', 4)
            > INSERT INTO tblProductDetai lsResult VALUES (8, 'JKL3', 9)
            > INSERT INTO tblProductDetai lsResult VALUES (9, 'JKL4', 10)
            >
            >
            > Thanks again
            >[color=green]
            >> You can rename your table tblproducts using sp_rename then use something
            >> like:
            >>
            >> INSERT INTO [dbo].[tblProducts] ( [UniqueID], [Name], TagId )
            >> select (SELECT COUNT(*)
            >> from [dbo].[oldtblProducts] Q
            >> JOIN [dbo].tblProductDeta ils E ON E.[ProductID] = Q.[UniqueID]
            >> WHERE D.[ProductID] > E.[ProductID]
            >> OR ( D.[ProductID] = E.[ProductID] AND D.Name > E.Name ) ) + 1 AS[/color]
            > UniqueID,[color=green]
            >> D.Name,P.TagNo
            >> from [dbo].[oldtblProducts] P
            >> JOIN [dbo].tblProductDeta ils D ON D.[ProductID] = P.[UniqueID][/color]
            >
            >
            >[/color]


            Comment

            • Erland Sommarskog

              #7
              Re: UPDATE/INSERT to make One-to-Many table become One-to-One

              serge (sergea@nospam. ehmail.com) writes:[color=blue]
              > I ran your code and even though the code looks very nice (a single
              > Insert statement), it doesn't work in my case. If you compare my
              > tblProductsResu lt and the result generated by your code, the UniqueIDs
              > are values that I can't modify for the already existing records in
              > tblProducts because they are referenced in other tables. My tblProducts
              > UniqueID 4 is "JKL", after running your code the UniqueID 4 is "GHI2"
              > which would cause problems for me.
              >
              > I also completely forgot to mention a bigger problem in my original
              > post. I would also need to update the ProductID values in
              > tblProductDetai ls for all the records that are being created in
              > tblProducts. So everytime a new record is created in tblProducts I will
              > need to get the new Identity value of tblProducts and update the
              > ProductID in tblProductDetai ls.
              >
              > I also just noticed the code I had originally posted doesn't have the
              > product 'MNO' for my sample example.
              >
              > Here's the whole new code again if anyone is interested to help me out.
              > I thought there could be a way to do this using a few update and insert
              > statements. I suspect now that maybe it is possible but much harder to
              > write than to write some type of a looping through the records one by
              > one cursor and do UPDATE or INSERT statements one record at a time.[/color]

              Below is a script that almost produces the result you are asking for.
              The difference from your reuslts table is that Product 4 now gets the
              name JKL2, and JKL is at Product 8. But since 4 and 8 are clones of
              each other, my guess is that it doesn't matter. If it does, it can
              probably be handled, but I didn't want to spend time to find how it
              it's not needed.

              -- A mapping table from old product id to new product id, as well as from
              -- detail id to new product id. The ident colunm is there to help us with
              -- the new ids.
              CREATE TABLE #newprod(ident int IDENTITY,
              oldprodid int NOT NULL,
              detailid int NOT NULL,
              name varchar(80) NOT NULL,
              newprodid int NULL)

              -- Insert all details where there detail id is bigger than the min
              -- detail-id for for a product.
              INSERT #newprod (oldprodid, detailid, name)
              SELECT a.ProductID, a.UniqueID, a.Name
              FROM tblProductDetai ls a
              WHERE NOT EXISTS (SELECT *
              FROM (SELECT id = MIN(UniqueID)
              FROM tblProductDetai ls
              GROUP BY ProductID) AS b
              WHERE a.UniqueID = b.id)
              ORDER BY a.UniqueID

              -- Set new the productid.
              UPDATE #newprod
              SET newprodid = n.ident + (SELECT MAX(UniqueID) FROM tblProducts)
              FROM #newprod n

              -- Create the clones. (If the real-world table has IDENTITY, you need
              -- SET IDENTITY_INSERT ON here.
              INSERT tblProducts (UniqueID, Name, TagNo)
              SELECT n.newprodid, p.Name, p.TagNo
              FROM #newprod n
              JOIN tblProducts p ON n.oldprodid = p.UniqueID

              -- Update details table with the new Product ids.
              UPDATE tblProductDetai ls
              SET ProductID = n.newprodid
              FROM tblProductDetai ls d
              JOIN #newprod n ON d.UniqueID = n.detailid

              -- Set product name to match details.
              UPDATE tblProducts
              SET Name = d.Name
              FROM tblProducts p
              JOIN tblProductDetai ls d ON p.UniqueID = d.ProductID

              -- And go on to clone components. Again there is an intermediate table
              -- to get the new identity values. Had the Component tables had an
              -- IDENTITY column, we would not need this table.
              CREATE TABLE #newcomp(ident int IDENTITY,
              newprodid int NOT NULL,
              component varchar(80) NOT NULL)

              INSERT #newcomp(newpro did, component)
              SELECT n.newprodid, c.Component
              FROM tblProductCompo nents c
              JOIN #newprod n ON c.ProductID = n.oldprodid
              ORDER BY n.newprodid, c.Component

              INSERT tblProductCompo nents (UniqueID, ProductID, Component)
              SELECT n.ident + m.maxid, n.newprodid, n.component
              FROM #newcomp n
              CROSS JOIN (SELECT maxid = MAX(UniqueID)
              FROM tblProductCompo nents) AS m

              -- Look at the result
              SELECT * FROM tblProducts ORDER BY UniqueID
              SELECT * FROM tblProductDetai ls ORDER BY UniqueID
              SELECT * FROM tblProductCompo nents ORDER BY UniqueID






              --
              Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

              Books Online for SQL Server SP3 at
              Get the flexibility you need to use integrated solutions, apps, and innovations in technology with your data, wherever it lives—in the cloud, on-premises, or at the edge.

              Comment

              • serge

                #8
                Re: UPDATE/INSERT to make One-to-Many table become One-to-One

                > The difference from your reuslts table is that Product 4 now gets the[color=blue]
                > name JKL2, and JKL is at Product 8. But since 4 and 8 are clones of
                > each other, my guess is that it doesn't matter.[/color]

                I am 99% sure it does not matter. I'll find out soon if it matters but I
                doubt it.

                I tried your code and it works great. I analyzed it for the last hour to
                understand what you were doing. Now I understand and I didn't consider
                before using a temporary table like you are using. I was planning on
                using a cursor and loop through the records one by one which would have
                been much slower than your approach. Not to mention it would have been
                complicated also.

                Next time I'll think about using temporary tables as I realize now how
                much flexibility it gave you to do the gradual data manipulations.


                Thanks a lot Erland, I appreciate your help.




                Comment

                • Erland Sommarskog

                  #9
                  Re: UPDATE/INSERT to make One-to-Many table become One-to-One

                  serge (sergea@nospam. ehmail.com) writes:[color=blue]
                  > I tried your code and it works great. I analyzed it for the last hour to
                  > understand what you were doing. Now I understand and I didn't consider
                  > before using a temporary table like you are using. I was planning on
                  > using a cursor and loop through the records one by one which would have
                  > been much slower than your approach. Not to mention it would have been
                  > complicated also.
                  >
                  > Next time I'll think about using temporary tables as I realize now how
                  > much flexibility it gave you to do the gradual data manipulations.[/color]

                  The true die-hards does of course try to all in one query, and stay
                  away from temp tables. I am more pragmatic, and interested in getting
                  the job done as effectively as possible.

                  The problem could probably be solved with out temp tables, if you don't
                  care about the new ids to be consecutive. The new product ids could be
                  formed by adding MAX(ProductID) to the detail id. But since you real
                  tables have IDENTITY, I figured that you could get into some problems
                  with that.


                  --
                  Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

                  Books Online for SQL Server SP3 at
                  Get the flexibility you need to use integrated solutions, apps, and innovations in technology with your data, wherever it lives—in the cloud, on-premises, or at the edge.

                  Comment

                  • serge

                    #10
                    Re: UPDATE/INSERT to make One-to-Many table become One-to-One

                    > Below is a script that almost produces the result you are asking for.[color=blue]
                    > The difference from your reuslts table is that Product 4 now gets the
                    > name JKL2, and JKL is at Product 8. But since 4 and 8 are clones of
                    > each other, my guess is that it doesn't matter. If it does, it can
                    > probably be handled, but I didn't want to spend time to find how it
                    > it's not needed.[/color]

                    Actually it seems that the name does matter. There is a unique index on
                    the NAME column on tblProducts. You make your code look easy and
                    simple to write. I tried to modify this part of the code below to exclude
                    matching names from the selection list by adding a JOIN to tblProducts.
                    I was unsuccessful after 3 hours of trying. I have people pressing me to
                    give up writing code and upgrade the data manually one record at a time.
                    What a nightmare!

                    I then thought about deleting temporarily the Unique Index and running the
                    code which will create duplicate records without any error. Then the UPDATE
                    Names code I believe is fixing/patching things up. However i am at this
                    point
                    unsure if i will have to face a new problem when it comes to the other CHILD
                    table references that I have to create clone records.
                    [color=blue]
                    > SELECT a.ProductID, a.UniqueID, a.Name
                    > FROM tblProductDetai ls a
                    > WHERE NOT EXISTS (SELECT *
                    > FROM (SELECT id = MIN(UniqueID)
                    > FROM tblProductDetai ls
                    > GROUP BY ProductID) AS b
                    > WHERE a.UniqueID = b.id)
                    > ORDER BY a.UniqueID[/color]

                    If you believe you can update the code very easily to handle the Name
                    matter,
                    would it be possible to do it please?

                    Thank you




                    Comment

                    • Erland Sommarskog

                      #11
                      Re: UPDATE/INSERT to make One-to-Many table become One-to-One

                      serge (sergea@nospam. ehmail.com) writes:[color=blue]
                      > Actually it seems that the name does matter. There is a unique index on
                      > the NAME column on tblProducts. You make your code look easy and
                      > simple to write. I tried to modify this part of the code below to exclude
                      > matching names from the selection list by adding a JOIN to tblProducts.
                      > I was unsuccessful after 3 hours of trying. I have people pressing me to
                      > give up writing code and upgrade the data manually one record at a time.
                      > What a nightmare!
                      >
                      > I then thought about deleting temporarily the Unique Index and running
                      > the code which will create duplicate records without any error. Then the
                      > UPDATE Names code I believe is fixing/patching things up. However i am
                      > at this point unsure if i will have to face a new problem when it comes
                      > to the other CHILD table references that I have to create clone
                      > records.[/color]

                      So here is a quick fix, that evades the problem with the constraint,
                      but still moves the id:

                      -- Create the clones. (If the real-world table has IDENTITY, you need
                      -- SET IDENTITY_INSERT ON here.
                      INSERT tblProducts (UniqueID, Name, TagNo)
                      SELECT n.newprodid, newid(), p.TagNo
                      FROM #newprod n
                      JOIN tblProducts p ON n.oldprodid = p.UniqueID

                      That is, I'm creating the clones with dummy names, to fix those
                      later.

                      I looked into preserving the name entire, but that was a little bit
                      tricky at this time of night.



                      --
                      Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

                      Books Online for SQL Server SP3 at
                      Get the flexibility you need to use integrated solutions, apps, and innovations in technology with your data, wherever it lives—in the cloud, on-premises, or at the edge.

                      Comment

                      • serge

                        #12
                        Re: UPDATE/INSERT to make One-to-Many table become One-to-One

                        > So here is a quick fix, that evades the problem with the constraint,[color=blue]
                        > but still moves the id:
                        >
                        > -- Create the clones. (If the real-world table has IDENTITY, you need
                        > -- SET IDENTITY_INSERT ON here.
                        > INSERT tblProducts (UniqueID, Name, TagNo)
                        > SELECT n.newprodid, newid(), p.TagNo
                        > FROM #newprod n
                        > JOIN tblProducts p ON n.oldprodid = p.UniqueID
                        >
                        > That is, I'm creating the clones with dummy names, to fix those
                        > later.[/color]

                        Thanks again for helping me.

                        What you are doing is nice. Something i didn't think of. Instead of
                        temporarily deleting the Index I could use your method.

                        However this still doesn't solve the problem with the name.

                        [color=blue]
                        > I looked into preserving the name entire, but that was a little bit
                        > tricky at this time of night.[/color]

                        I continued this from home and it is much easier working at home than
                        at work. I can think without any disruption.

                        I believe I have the code that returns all the records that I will
                        need to ADD as new records and not worry of the name issue.

                        I am replacing this block of code:

                        -- Insert all details where there detail id is bigger than the min
                        -- detail-id for for a product.
                        INSERT #newprod (oldprodid, detailid, name)
                        SELECT a.ProductID, a.UniqueID, a.Name
                        FROM tblProductDetai ls a
                        WHERE NOT EXISTS (SELECT *
                        FROM (SELECT id = MIN(UniqueID)
                        FROM tblProductDetai ls
                        GROUP BY ProductID) AS b
                        WHERE a.UniqueID = b.id)
                        ORDER BY a.UniqueID


                        with this new one:

                        -- Insert all details where there detail id is bigger than the min
                        -- detail-id for for a product.
                        INSERT #newprod (oldprodid, detailid, name)
                        SELECT a.ProductID, a.UniqueID, a.Name
                        FROM

                        (SELECT tblProductDetai ls.ProductId, tblProductDetai ls.UniqueId,
                        tblProductDetai ls.Name
                        FROM tblProductDetai ls INNER JOIN tblProducts
                        ON tblProductDetai ls.ProductId = tblProducts.Uni queId
                        WHERE tblProductDetai ls.Name <> tblProducts.Nam e) AS a

                        INNER JOIN

                        (SELECT ProductID
                        FROM tblProductDetai ls
                        GROUP BY ProductID
                        HAVING COUNT(*) > 1) AS b
                        ON a.ProductId = b.ProductId

                        ORDER BY a.UniqueID


                        Basically my first derived table "a" returns me all tblProductDetai ls where
                        the
                        Name is different than the Name in tblProducts.

                        Then I need to reduce the list of the derived table "a" to those records
                        that
                        exist more than 1 record (JOIN to derived table "b").


                        Anyhow I think the name problem is resolved. I'll continue working and I'll
                        see
                        how things go. I still have some other conditions that I'll have to decide
                        if I
                        can deal with code or I have to manually fix it.

                        Thanks Erland.





                        Comment

                        • --CELKO--

                          #13
                          Re: UPDATE/INSERT to make One-to-Many table become One-to-One

                          Hope I helped a little bit.

                          The fifth labor of Hercules was to clean the stables of King Augeas in
                          a single day. The Augean stables held thousands of animals and were
                          over a mile long. This story has a happy ending for three reasons: (1)
                          Hercules solved the problem in a clever way (2) Hercules got one tenth
                          of the cattle for his work (3) At the end of the story of the Labors of
                          Hercules, he got to kill the bastard that gave him this job.

                          Comment

                          • Erland Sommarskog

                            #14
                            Re: UPDATE/INSERT to make One-to-Many table become One-to-One

                            serge (sergea@nospam. ehmail.com) writes:[color=blue]
                            > with this new one:
                            >
                            > -- Insert all details where there detail id is bigger than the min
                            > -- detail-id for for a product.
                            > INSERT #newprod (oldprodid, detailid, name)
                            > SELECT a.ProductID, a.UniqueID, a.Name
                            > FROM
                            >
                            > (SELECT tblProductDetai ls.ProductId, tblProductDetai ls.UniqueId,
                            > tblProductDetai ls.Name
                            > FROM tblProductDetai ls INNER JOIN tblProducts
                            > ON tblProductDetai ls.ProductId = tblProducts.Uni queId
                            > WHERE tblProductDetai ls.Name <> tblProducts.Nam e) AS a
                            >
                            > INNER JOIN
                            >
                            > (SELECT ProductID
                            > FROM tblProductDetai ls
                            > GROUP BY ProductID
                            > HAVING COUNT(*) > 1) AS b
                            > ON a.ProductId = b.ProductId
                            >
                            > ORDER BY a.UniqueID
                            >
                            >
                            > Basically my first derived table "a" returns me all tblProductDetai ls
                            > where the Name is different than the Name in tblProducts.
                            >
                            > Then I need to reduce the list of the derived table "a" to those records
                            > that exist more than 1 record (JOIN to derived table "b").[/color]

                            Excellent! Don't know if I would have been able to come with that
                            one by myself. (Well, maybe I would, but I was not thinking those
                            directions.)

                            Here is a rewrite of the query, more for clarity to tell exactly
                            what is going on: Save all product details where the name differs
                            from the product, for which there is more than one row for the
                            product:

                            INSERT #newprod (oldprodid, detailid, name)
                            SELECT a.ProductID, a.UniqueID, a.Name
                            FROM (SELECT d.ProductID, d.UniqueID, d.Name
                            FROM tblProductDetai ls d
                            JOIN tblProducts p ON d.ProductID = p.UniqueID
                            WHERE d.Name <> p.Name) AS a
                            WHERE EXISTS (SELECT *
                            FROM (SELECT ProductID
                            FROM tblProductDetai ls
                            GROUP BY ProductID
                            HAVING COUNT(*) > 1) AS b
                            WHERE a.ProductID = b.ProductID)
                            ORDER BY a.UniqueID

                            Whether one likes alias or is partly a matter of taste, but I think
                            the code is more consice and easier to read this way.

                            Thanks for posting back the final result!

                            --
                            Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

                            Books Online for SQL Server SP3 at
                            Get the flexibility you need to use integrated solutions, apps, and innovations in technology with your data, wherever it lives—in the cloud, on-premises, or at the edge.

                            Comment

                            • serge

                              #15
                              Re: UPDATE/INSERT to make One-to-Many table become One-to-One

                              > Here is a rewrite of the query, more for clarity to tell exactly[color=blue]
                              > what is going on: Save all product details where the name differs
                              > from the product, for which there is more than one row for the
                              > product:[/color]

                              Thanks Erland, I will look at this new code.



                              Comment

                              Working...