locked
updating values in one table according to values from 2 other tables (HELP!) RRS feed

  • Question

  • Hi, I am new to Sql server and I have been trying this question for almost 2 days now! I searched everywhere on google but can't seem to find a decent answer :(

    My problem is that I have 2 tables, one is named my_contacts and the other is interests. my_contacts ( P_id, lastname, firstname, interest1, interest2, interest3) and interests(interest_id, interest). As you can see, the my_contacts is not in 1NF because of the repeating columns interest1, interest2,interest3 (one person may have more than 1 interest).

    So, I thought I would make another contact_interest table where contact_interest(P_id, interest_id) so i can remove the interest1/2/3 from my_contacts. But, I dont know how to do that!! i tried inner joins but it doesnt seem to work!

    Here is the syntax I tried:


    update contact_interest
    set contact_interest.interest_id = i.int_id
    from my_contacts mc
    inner join interest i
    on mc.interest1 = i.interests;

    HELP!!

     

    • Changed type cindyak Tuesday, May 31, 2011 2:01 AM
    Monday, May 30, 2011 9:32 AM

Answers

  • hi,

    according to your description, you need an Interest table like

    Interests (Interest_ID, P_ID, Interest)

    to normalize it. Your approach of Interests and Conact_Interests is the Domain Key Normal Form

    Using your approach you need

    1) fill the Interests table 2) fill the junction table

    -- 1)
    WITH InterestsFromContacts AS
    ( SELECT Interest1 AS Interest FROM my_contacts
      UNION
      SELECT Interest2 FROM my_contacts
      UNION
      SELECT Interest1 FROM my_contacts
    )
    INSERT INTO Interests (Interest)
    SELECT Interest
    FROM   InterestsFromContacts
    ORDER BY 1 ;
    
    -- 2)
    INSERT INTO Contact_Interest
    SELECT C.P_ID, I.Interest_ID
    FROM   my_contacts C
           INNER JOIN Interests I ON C.Interest1 = I.Interest ;
    
    INSERT INTO Contact_Interest
    SELECT C.P_ID, I.Interest_ID
    FROM   my_contacts C
           INNER JOIN Interests I ON C.Interest2 = I.Interest ;
    
    INSERT INTO Contact_Interest
    SELECT C.P_ID, I.Interest_ID
    FROM   my_contacts C
           INNER JOIN Interests I ON C.Interest3 = I.Interest ;

    Microsoft MVP Office Access
    https://mvp.support.microsoft.com/profile/Stefan.Hoffmann
    • Marked as answer by cindyak Tuesday, May 31, 2011 2:01 AM
    Monday, May 30, 2011 9:45 AM

All replies

  • But you want to insert rows into newlly added table not to update them so use insert, below code is not tested but should work:

    insert into contact_interest select p_id, interest1 from my_contacts mc inner join interest i on (mc.interest1 = i.int_id) where interest1 is not null

     

     


    With kind regards
    Krystian Zieja
    http://www.projectnenvision.com
    Follow me on twitter
    My Blog
    Monday, May 30, 2011 9:40 AM
  • hi,

    according to your description, you need an Interest table like

    Interests (Interest_ID, P_ID, Interest)

    to normalize it. Your approach of Interests and Conact_Interests is the Domain Key Normal Form

    Using your approach you need

    1) fill the Interests table 2) fill the junction table

    -- 1)
    WITH InterestsFromContacts AS
    ( SELECT Interest1 AS Interest FROM my_contacts
      UNION
      SELECT Interest2 FROM my_contacts
      UNION
      SELECT Interest1 FROM my_contacts
    )
    INSERT INTO Interests (Interest)
    SELECT Interest
    FROM   InterestsFromContacts
    ORDER BY 1 ;
    
    -- 2)
    INSERT INTO Contact_Interest
    SELECT C.P_ID, I.Interest_ID
    FROM   my_contacts C
           INNER JOIN Interests I ON C.Interest1 = I.Interest ;
    
    INSERT INTO Contact_Interest
    SELECT C.P_ID, I.Interest_ID
    FROM   my_contacts C
           INNER JOIN Interests I ON C.Interest2 = I.Interest ;
    
    INSERT INTO Contact_Interest
    SELECT C.P_ID, I.Interest_ID
    FROM   my_contacts C
           INNER JOIN Interests I ON C.Interest3 = I.Interest ;

    Microsoft MVP Office Access
    https://mvp.support.microsoft.com/profile/Stefan.Hoffmann
    • Marked as answer by cindyak Tuesday, May 31, 2011 2:01 AM
    Monday, May 30, 2011 9:45 AM
  • Thanks for the fast reply, I will try it right away and let you know if it works!
    Monday, May 30, 2011 9:49 AM
  • See if that helps

    create table #t1 (cust int, address1 char(1),address2 char(1),address3 char(1))

    create table #t2 (addressid int, address_n char(1))

     

    insert into  #t1 values (1,'a','b','c')

    insert into  #t1 values (2,'d','e','f')

     

    insert into   #t2 values (1,'a')

    insert into   #t2 values (2,'b')

    insert into   #t2 values (3,'c')

    insert into   #t2 values (4,'d')

    insert into   #t2 values (5,'e')

    insert into   #t2 values (6,'f')

     

     

    create table #t3 (cust int,addressid int)

     

    with cte

    as

    (

    select * from #t1 join #t2 on 

    #t2.address_n=#t1.address1

    or  #t2.address_n=#t1.address2

    or #t2.address_n=#t1.address3

    ) insert into #t3 (cust,addressid)

      select cust,addressid from cte

     

     

    select * from #t3


    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    Monday, May 30, 2011 9:54 AM
    Answerer
  • It's working, but the problem is that I had already filled in the Interests table manually ( I didn't know which T-sql code to use that time) and using your code above made duplicate rows in my Interests table. Also my Interest_ID is auto-incremented using identity(1,1) when I created the table. Is there a way to delete the duplicate rows? Thanks a lot!

    Monday, May 30, 2011 10:13 AM
  • See my reply, if it does not help,please provide  sample data+desired result. Always state what version you are using
    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    Monday, May 30, 2011 10:34 AM
    Answerer
  • hi,

    delete all rows and use DBCC CHECKIDENT to reseed your IDENTITY column, e.g.

    DBCC CHECKIDENT ("Interests", RESEED, 1);

    Microsoft MVP Office Access
    https://mvp.support.microsoft.com/profile/Stefan.Hoffmann
    Monday, May 30, 2011 11:20 AM
  • @Uri: Hi, thanks for the code, but I am beginner to T-Sql, so I don't really understand your code...and it doesnt seem to have anything to do with my problem....but thanks though! Maybe it's me!
    Monday, May 30, 2011 2:03 PM
  • @Stefan H: Thanks a lot! It worked! But you see, according to my table, some people have null values in their interest3, that is they have only two interests in the table. The interest_id starts at value 2 with corresponding interest being NULL. I tried to fix using the following:

    with InterestsFromContacts as
    ( select interest1 as interest from my_contacts
     union
    select interest2 as interest from my_contacts
    union
    select interest3 as interest from my_contacts
    )
    Insert into Interests(interest)
    select interest
    from InterestsFromContacts ifc
    where ifc.interest is not null
    order by 1;

    But, the interest_id still starts at value 2.

    Thanks!

    Monday, May 30, 2011 2:05 PM
  • DBCC CHECKIDENT('Interests',RESEED,0);

    works though but I dont quite understand why because the new_seed_value is supposed to be set at 1 isnt it?

     

    Monday, May 30, 2011 2:11 PM
  • RESEED sets the last used value. So that the next INSERT uses this last used value + 1 , e.g.

    CREATE TABLE #Sample
        (
          ID INT IDENTITY(1, 1) ,
          Payload NVARCHAR(32)
        ) ;
    
    INSERT  INTO #Sample
    VALUES  ( 'a' ) ,
            ( 'b' ),
            ( 'c' ),
            ( 'd' ),
            ( 'e' ) ;
    
    SELECT  *
    FROM    #Sample ;
    
    DELETE  FROM #Sample ;
    
    INSERT  INTO #Sample
    VALUES  ( 'a' ) ,
            ( 'b' ),
            ( 'c' ) ;
    
    SELECT  *
    FROM    #Sample ;
    
    DELETE  FROM #Sample ;
    
    DBCC CHECKIDENT ('#Sample', RESEED, 1) ;
    
    INSERT  INTO #Sample
    VALUES  ( 'a' ) ,
            ( 'b' ),
            ( 'c' ) ;
    
    SELECT  *
    FROM    #Sample ;
    
    DELETE  FROM #Sample ;
    
    DBCC CHECKIDENT ('#Sample', RESEED, 0) ;
    
    INSERT  INTO #Sample
    VALUES  ( 'a' ) ,
            ( 'b' ),
            ( 'c' ) ;
    
    SELECT  *
    FROM    #Sample ;
    
    DROP TABLE #Sample ;

    Microsoft MVP Office Access
    https://mvp.support.microsoft.com/profile/Stefan.Hoffmann
    Monday, May 30, 2011 2:17 PM
  • @stefan H:I finally understood it! Thanks!
    Tuesday, May 31, 2011 2:00 AM