Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

Update Qry question

Discussion in 'Information Technology' started by JOM, Nov 16, 2005.

  1. JOM

    JOM
    Expand Collapse
    Guest

    I would like to to update 3 tables at once with a single field from a 4th
    table is this possible?
     
  2. Loading...


  3. Douglas J Steele

    Douglas J Steele
    Expand Collapse
    Guest

    I don't believe it is.

    --
    Doug Steele, Microsoft Access MVP
    http://I.Am/DougSteele
    (no e-mails, please!)


    "JOM" <JOM@discussions.microsoft.com> wrote in message
    news:21A49B5A-960C-47BF-967E-18D187D5B79D@microsoft.com...
    > I would like to to update 3 tables at once with a single field from a 4th
    > table is this possible?
     
  4. JOM

    JOM
    Expand Collapse
    Guest

    Ok, so how do I update/move tbl1's field with/to tbl2's field

    "Douglas J Steele" wrote:

    > I don't believe it is.
    >
    > --
    > Doug Steele, Microsoft Access MVP
    > http://I.Am/DougSteele
    > (no e-mails, please!)
    >
    >
    > "JOM" <JOM@discussions.microsoft.com> wrote in message
    > news:21A49B5A-960C-47BF-967E-18D187D5B79D@microsoft.com...
    > > I would like to to update 3 tables at once with a single field from a 4th
    > > table is this possible?

    >
    >
    >
     
  5. Douglas J Steele

    Douglas J Steele
    Expand Collapse
    Guest

    Try something like:

    UPDATE Table1 INNER JOIN Table2
    ON Table1.ID = Table2.ID
    SET Field1 = Table2.Field2
    WHERE Field2 = 12343

    --
    Doug Steele, Microsoft Access MVP
    http://I.Am/DougSteele
    (no e-mails, please!)


    "JOM" <JOM@discussions.microsoft.com> wrote in message
    news:3F648D7A-B840-47C8-B341-EDCA575C60B9@microsoft.com...
    > Ok, so how do I update/move tbl1's field with/to tbl2's field
    >
    > "Douglas J Steele" wrote:
    >
    > > I don't believe it is.
    > >
    > > --
    > > Doug Steele, Microsoft Access MVP
    > > http://I.Am/DougSteele
    > > (no e-mails, please!)
    > >
    > >
    > > "JOM" <JOM@discussions.microsoft.com> wrote in message
    > > news:21A49B5A-960C-47BF-967E-18D187D5B79D@microsoft.com...
    > > > I would like to to update 3 tables at once with a single field from a

    4th
    > > > table is this possible?

    > >
    > >
    > >
     
  6. JOM

    JOM
    Expand Collapse
    Guest

    ok here is my query
    UPDATE Table1 INNER JOIN Table2 ON Table1.NameID = Table2.AdvantageID SET
    Table1.Name = Table2.AdvantageName
    WHERE (((Table1.Name)="Fruits"));
    but when I run it it say 0 records therefore does not update the table2
    Why?

    "Douglas J Steele" wrote:

    > Try something like:
    >
    > UPDATE Table1 INNER JOIN Table2
    > ON Table1.ID = Table2.ID
    > SET Field1 = Table2.Field2
    > WHERE Field2 = 12343
    >
    > --
    > Doug Steele, Microsoft Access MVP
    > http://I.Am/DougSteele
    > (no e-mails, please!)
    >
    >
    > "JOM" <JOM@discussions.microsoft.com> wrote in message
    > news:3F648D7A-B840-47C8-B341-EDCA575C60B9@microsoft.com...
    > > Ok, so how do I update/move tbl1's field with/to tbl2's field
    > >
    > > "Douglas J Steele" wrote:
    > >
    > > > I don't believe it is.
    > > >
    > > > --
    > > > Doug Steele, Microsoft Access MVP
    > > > http://I.Am/DougSteele
    > > > (no e-mails, please!)
    > > >
    > > >
    > > > "JOM" <JOM@discussions.microsoft.com> wrote in message
    > > > news:21A49B5A-960C-47BF-967E-18D187D5B79D@microsoft.com...
    > > > > I would like to to update 3 tables at once with a single field from a

    > 4th
    > > > > table is this possible?
    > > >
    > > >
    > > >

    >
    >
    >
     
  7. Douglas J. Steele

    Douglas J. Steele
    Expand Collapse
    Guest

    You sure you have records in Table1 with Name = Fruits?

    --
    Doug Steele, Microsoft Access MVP
    http://I.Am/DougSteele
    (no e-mails, please!)



    "JOM" <JOM@discussions.microsoft.com> wrote in message
    news:33162F24-A680-40C5-B7EF-78106BCBB704@microsoft.com...
    > ok here is my query
    > UPDATE Table1 INNER JOIN Table2 ON Table1.NameID = Table2.AdvantageID SET
    > Table1.Name = Table2.AdvantageName
    > WHERE (((Table1.Name)="Fruits"));
    > but when I run it it say 0 records therefore does not update the table2
    > Why?
    >
    > "Douglas J Steele" wrote:
    >
    >> Try something like:
    >>
    >> UPDATE Table1 INNER JOIN Table2
    >> ON Table1.ID = Table2.ID
    >> SET Field1 = Table2.Field2
    >> WHERE Field2 = 12343
    >>
    >> --
    >> Doug Steele, Microsoft Access MVP
    >> http://I.Am/DougSteele
    >> (no e-mails, please!)
    >>
    >>
    >> "JOM" <JOM@discussions.microsoft.com> wrote in message
    >> news:3F648D7A-B840-47C8-B341-EDCA575C60B9@microsoft.com...
    >> > Ok, so how do I update/move tbl1's field with/to tbl2's field
    >> >
    >> > "Douglas J Steele" wrote:
    >> >
    >> > > I don't believe it is.
    >> > >
    >> > > --
    >> > > Doug Steele, Microsoft Access MVP
    >> > > http://I.Am/DougSteele
    >> > > (no e-mails, please!)
    >> > >
    >> > >
    >> > > "JOM" <JOM@discussions.microsoft.com> wrote in message
    >> > > news:21A49B5A-960C-47BF-967E-18D187D5B79D@microsoft.com...
    >> > > > I would like to to update 3 tables at once with a single field from
    >> > > > a

    >> 4th
    >> > > > table is this possible?
    >> > >
    >> > >
    >> > >

    >>
    >>
    >>
     
  8. JOM

    JOM
    Expand Collapse
    Guest

    yes I do, but what I want is get all the records in table1.name and
    append/update table2.advantagename

    "Douglas J. Steele" wrote:

    > You sure you have records in Table1 with Name = Fruits?
    >
    > --
    > Doug Steele, Microsoft Access MVP
    > http://I.Am/DougSteele
    > (no e-mails, please!)
    >
    >
    >
    > "JOM" <JOM@discussions.microsoft.com> wrote in message
    > news:33162F24-A680-40C5-B7EF-78106BCBB704@microsoft.com...
    > > ok here is my query
    > > UPDATE Table1 INNER JOIN Table2 ON Table1.NameID = Table2.AdvantageID SET
    > > Table1.Name = Table2.AdvantageName
    > > WHERE (((Table1.Name)="Fruits"));
    > > but when I run it it say 0 records therefore does not update the table2
    > > Why?
    > >
    > > "Douglas J Steele" wrote:
    > >
    > >> Try something like:
    > >>
    > >> UPDATE Table1 INNER JOIN Table2
    > >> ON Table1.ID = Table2.ID
    > >> SET Field1 = Table2.Field2
    > >> WHERE Field2 = 12343
    > >>
    > >> --
    > >> Doug Steele, Microsoft Access MVP
    > >> http://I.Am/DougSteele
    > >> (no e-mails, please!)
    > >>
    > >>
    > >> "JOM" <JOM@discussions.microsoft.com> wrote in message
    > >> news:3F648D7A-B840-47C8-B341-EDCA575C60B9@microsoft.com...
    > >> > Ok, so how do I update/move tbl1's field with/to tbl2's field
    > >> >
    > >> > "Douglas J Steele" wrote:
    > >> >
    > >> > > I don't believe it is.
    > >> > >
    > >> > > --
    > >> > > Doug Steele, Microsoft Access MVP
    > >> > > http://I.Am/DougSteele
    > >> > > (no e-mails, please!)
    > >> > >
    > >> > >
    > >> > > "JOM" <JOM@discussions.microsoft.com> wrote in message
    > >> > > news:21A49B5A-960C-47BF-967E-18D187D5B79D@microsoft.com...
    > >> > > > I would like to to update 3 tables at once with a single field from
    > >> > > > a
    > >> 4th
    > >> > > > table is this possible?
    > >> > >
    > >> > >
    > >> > >
    > >>
    > >>
    > >>

    >
    >
    >
     
  9. Douglas J. Steele

    Douglas J. Steele
    Expand Collapse
    Guest

    Huh? I don't understand what you mean.

    Can you give an example, with sample data?

    --
    Doug Steele, Microsoft Access MVP
    http://I.Am/DougSteele
    (no e-mails, please!)



    "JOM" <JOM@discussions.microsoft.com> wrote in message
    news:3F7B0C0F-0956-4354-A23D-7ABAD345FC09@microsoft.com...
    > yes I do, but what I want is get all the records in table1.name and
    > append/update table2.advantagename
    >
    > "Douglas J. Steele" wrote:
    >
    >> You sure you have records in Table1 with Name = Fruits?
    >>
    >> --
    >> Doug Steele, Microsoft Access MVP
    >> http://I.Am/DougSteele
    >> (no e-mails, please!)
    >>
    >>
    >>
    >> "JOM" <JOM@discussions.microsoft.com> wrote in message
    >> news:33162F24-A680-40C5-B7EF-78106BCBB704@microsoft.com...
    >> > ok here is my query
    >> > UPDATE Table1 INNER JOIN Table2 ON Table1.NameID = Table2.AdvantageID
    >> > SET
    >> > Table1.Name = Table2.AdvantageName
    >> > WHERE (((Table1.Name)="Fruits"));
    >> > but when I run it it say 0 records therefore does not update the table2
    >> > Why?
    >> >
    >> > "Douglas J Steele" wrote:
    >> >
    >> >> Try something like:
    >> >>
    >> >> UPDATE Table1 INNER JOIN Table2
    >> >> ON Table1.ID = Table2.ID
    >> >> SET Field1 = Table2.Field2
    >> >> WHERE Field2 = 12343
    >> >>
    >> >> --
    >> >> Doug Steele, Microsoft Access MVP
    >> >> http://I.Am/DougSteele
    >> >> (no e-mails, please!)
    >> >>
    >> >>
    >> >> "JOM" <JOM@discussions.microsoft.com> wrote in message
    >> >> news:3F648D7A-B840-47C8-B341-EDCA575C60B9@microsoft.com...
    >> >> > Ok, so how do I update/move tbl1's field with/to tbl2's field
    >> >> >
    >> >> > "Douglas J Steele" wrote:
    >> >> >
    >> >> > > I don't believe it is.
    >> >> > >
    >> >> > > --
    >> >> > > Doug Steele, Microsoft Access MVP
    >> >> > > http://I.Am/DougSteele
    >> >> > > (no e-mails, please!)
    >> >> > >
    >> >> > >
    >> >> > > "JOM" <JOM@discussions.microsoft.com> wrote in message
    >> >> > > news:21A49B5A-960C-47BF-967E-18D187D5B79D@microsoft.com...
    >> >> > > > I would like to to update 3 tables at once with a single field
    >> >> > > > from
    >> >> > > > a
    >> >> 4th
    >> >> > > > table is this possible?
    >> >> > >
    >> >> > >
    >> >> > >
    >> >>
    >> >>
    >> >>

    >>
    >>
    >>
     
  10. Van T. Dinh

    Van T. Dinh
    Expand Collapse
    Guest

    The SQL specifies updating Table1, NOT Table2.

    The Fields to be updated must be on the LHS of the assignment statement in
    the SET clause.

    Doug's example updates Table1 so you need re-structure it if you want to
    update Table2.

    --
    HTH
    Van T. Dinh
    MVP (Access)



    "JOM" <JOM@discussions.microsoft.com> wrote in message
    news:33162F24-A680-40C5-B7EF-78106BCBB704@microsoft.com...
    > ok here is my query
    > UPDATE Table1 INNER JOIN Table2 ON Table1.NameID = Table2.AdvantageID SET
    > Table1.Name = Table2.AdvantageName
    > WHERE (((Table1.Name)="Fruits"));
    > but when I run it it say 0 records therefore does not update the table2
    > Why?
    >
     

Share This Page