Joining one to many relational table

Discussion in 'MCAD' started by sqllearner, Jun 4, 2005.

  1. sqllearner

    sqllearner Guest

    I have two tables: Table1 has A,A,B,B,B and Table2 has A, B. I want to join
    Table1 with Table2 and update Table1, but I want to update only 1 for A and 1
    for B in Table1. How will I do it?

    For Table1 the values for column1 is A,A,B,B,B and the correspoding values
    in column2 is 2,2,3,3,3 and the correspoding values in column3 is 0,0,0,0,0.
    In Table2 the values for column1 is A,B and the correspoding values in
    column2 is 2,3.

    I want to update Table1.Column3 = 1, where Table1.Column1 = Table2.Column1
    and Table1.Column2 = Table2.Column2. But using joins, I found it updates all
    5 rows in Table1.Column3. But I want to update only 2 rows in Table1.Column3,
    as Table2 has 2 rows. More specifically, I want to update one to one in this
    one to many relational tables.
    sqllearner, Jun 4, 2005
    #1
    1. Advertising

  2. Hi Learner,

    If you want to update only one of several matching rows then you'll have to
    find a way to specify exactly which row to update. Do the rows have a
    primary key? Could you target the row with the Min(Pk) where all the other
    conditions are met?

    --
    Cindy Winegarden MCSD, Microsoft Visual Foxpro MVP
    www.cindywinegarden.com
    Blog: http://spaces.msn.com/members/cindywinegarden


    "sqllearner" <> wrote in message
    news:...
    >I have two tables: Table1 has A,A,B,B,B and Table2 has A, B. I want to join
    > Table1 with Table2 and update Table1, but I want to update only 1 for A
    > and 1
    > for B in Table1. How will I do it?
    >
    > For Table1 the values for column1 is A,A,B,B,B and the correspoding values
    > in column2 is 2,2,3,3,3 and the correspoding values in column3 is
    > 0,0,0,0,0.
    > In Table2 the values for column1 is A,B and the correspoding values in
    > column2 is 2,3.
    >
    > I want to update Table1.Column3 = 1, where Table1.Column1 = Table2.Column1
    > and Table1.Column2 = Table2.Column2. But using joins, I found it updates
    > all
    > 5 rows in Table1.Column3. But I want to update only 2 rows in
    > Table1.Column3,
    > as Table2 has 2 rows. More specifically, I want to update one to one in
    > this
    > one to many relational tables.
    Cindy Winegarden, Jun 5, 2005
    #2
    1. Advertising

  3. sqllearner

    Changgyu Oh Guest

    Best way to do it is, I believe, to use a cursor.
    Since your table1 violates the first normalization, join query does not
    solve your problem.

    "Cindy Winegarden" <> wrote in message
    news:...
    > Hi Learner,
    >
    > If you want to update only one of several matching rows then you'll have
    > to find a way to specify exactly which row to update. Do the rows have a
    > primary key? Could you target the row with the Min(Pk) where all the other
    > conditions are met?
    >
    > --
    > Cindy Winegarden MCSD, Microsoft Visual Foxpro MVP
    > www.cindywinegarden.com
    > Blog: http://spaces.msn.com/members/cindywinegarden
    >
    >
    > "sqllearner" <> wrote in message
    > news:...
    >>I have two tables: Table1 has A,A,B,B,B and Table2 has A, B. I want to
    >>join
    >> Table1 with Table2 and update Table1, but I want to update only 1 for A
    >> and 1
    >> for B in Table1. How will I do it?
    >>
    >> For Table1 the values for column1 is A,A,B,B,B and the correspoding
    >> values
    >> in column2 is 2,2,3,3,3 and the correspoding values in column3 is
    >> 0,0,0,0,0.
    >> In Table2 the values for column1 is A,B and the correspoding values in
    >> column2 is 2,3.
    >>
    >> I want to update Table1.Column3 = 1, where Table1.Column1 =
    >> Table2.Column1
    >> and Table1.Column2 = Table2.Column2. But using joins, I found it updates
    >> all
    >> 5 rows in Table1.Column3. But I want to update only 2 rows in
    >> Table1.Column3,
    >> as Table2 has 2 rows. More specifically, I want to update one to one in
    >> this
    >> one to many relational tables.

    >
    >
    Changgyu Oh, Jun 9, 2005
    #3
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Joachim Krais
    Replies:
    2
    Views:
    14,868
    Andre Beck
    Nov 23, 2003
  2. John Ramsden
    Replies:
    0
    Views:
    825
    John Ramsden
    Jul 24, 2004
  3. =?Utf-8?B?c3FsbGVhcm5lcg==?=

    Joining one to many relational table

    =?Utf-8?B?c3FsbGVhcm5lcg==?=, Jun 4, 2005, in forum: MCSD
    Replies:
    2
    Views:
    444
    =?Utf-8?B?c3FsbGVhcm5lcg==?=
    Jun 4, 2005
  4. =?Utf-8?B?c3FsbGVhcm5lcg==?=

    Joining one to many relational table

    =?Utf-8?B?c3FsbGVhcm5lcg==?=, Jun 4, 2005, in forum: MCSE
    Replies:
    9
    Views:
    489
    FrisbeeĀ®
    Jun 7, 2005
  5. sqllearner
    Replies:
    0
    Views:
    249
    sqllearner
    Jun 4, 2005
Loading...

Share This Page