Welcome to SPN

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

Sign Up Now!
  1.   Become a Supporter    ::   Make a Contribution   
    Target (Recurring Monthly): $200 :: Achieved: $98

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?
    >
     
Since you're here... we have a small favor to ask...

More people are visiting & reading SPN than ever but far fewer are paying to sustain it. Advertising revenues across the online media have fallen fast. So you can see why we need to ask for your help. Donating to SPN's is vote for free speech, for diversity of opinions, for the right of the people to stand up to religious bigotry. Without any affiliation to any organization, this constant struggle takes a lot of hard work to sustain as we entirely depend on the contributions of our esteemed writers/readers. We do it because we believe our perspective matters – because it might well be your perspective, too... Fund our efforts and together we can keep the world informed about the real Sikh Sikhi Sikhism. If everyone who writes or reads our content, who likes it, helps us to pay for it, our future would be much more secure. Every Contribution Matters, Contribute Generously!

    Become a Supporter      ::     Make a Contribution     



Share This Page