SitePoint Sponsor

User Tag List

Results 1 to 12 of 12
  1. #1
    SitePoint Member
    Join Date
    Feb 2012
    Posts
    13
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Update with a join?

    Hello I am trying todo the following:

    Code MySQL:
    UPDATE Post_Gallery post_id 
    SET pg.post_id = c.post_id 
    FROM _Content AS c 
    WHERE c.id = pg.id

    However I get an error at 'FROM _Content....'

    Any help is much appreciated

  2. #2
    SitePoint Wizard guelphdad's Avatar
    Join Date
    Oct 2003
    Location
    St. Catharines, ON Canada
    Posts
    1,707
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Code:
    UPDATE Post_Gallery, Content SET Post_Gallery.post_id = Content.post_id  WHERE Content.id = Post_Gallery.id

  3. #3
    SitePoint Member
    Join Date
    Feb 2012
    Posts
    13
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Amazing, thank you

  4. #4
    SitePoint Enthusiast
    Join Date
    Aug 2011
    Location
    OH, USA
    Posts
    72
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    or like that


    UPDATE p
    SET p.post_id = c.post_id
    FROM Post_Gallery p join _Content AS c
    on p.id=c.id

  5. #5
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,216
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by gk53 View Post
    or like that
    no, not like that -- did you see post #1? the use of FROM is invalid
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  6. #6
    SitePoint Enthusiast
    Join Date
    Aug 2011
    Location
    OH, USA
    Posts
    72
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    no, not like that -- did you see post #1? the use of FROM is invalid
    It is work...

    you have

    UPDATE Post_Gallery post_id
    SET pg.post_id = c.post_id
    FROM _Content AS c
    WHERE c.id = pg.id

    and in from you must join both tables this is where the problem

    update tableWhichNeedsUpdate
    set <value=value>
    from table1 join table2
    on table.field=table2.field
    where <condition>

    this is a schema

  7. #7
    Hosting Team Leader silver trophybronze trophy
    cpradio's Avatar
    Join Date
    Jun 2002
    Location
    Ohio
    Posts
    5,069
    Mentioned
    152 Post(s)
    Tagged
    0 Thread(s)
    Seems to be a MySQL versus MS SQL situation again.

    From the MySQL page, UPDATE doesn't allow FROM
    However, MS SQL does.
    Be sure to congratulate Patche on earning July's Member of the Month
    Go ahead and blame me, I still won't lose any sleep over it
    My Blog | My Technical Notes

  8. #8
    SitePoint Wizard guelphdad's Avatar
    Join Date
    Oct 2003
    Location
    St. Catharines, ON Canada
    Posts
    1,707
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    This is why I wish sitepoint hadn't merged mysql in with the other databases forum when they "tidied" up the forums. Either that or everyone should preface their question with choice of database app in a pull-down menu that is a required field before posting.

    SIGH

  9. #9
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,216
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by cpradio View Post
    Seems to be a MySQL versus MS SQL situation again.
    but if one reads post #1 carefully, one notices that the error message pointed to the FROM keyword as causing the error

    ergo...
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  10. #10
    Hosting Team Leader silver trophybronze trophy
    cpradio's Avatar
    Join Date
    Jun 2002
    Location
    Ohio
    Posts
    5,069
    Mentioned
    152 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by guelphdad View Post
    This is why I wish sitepoint hadn't merged mysql in with the other databases forum when they "tidied" up the forums. Either that or everyone should preface their question with choice of database app in a pull-down menu that is a required field before posting.

    SIGH
    Off Topic:

    I have shared your suggestion with the Staff


    Quote Originally Posted by r937 View Post
    but if one reads post #1 carefully, one notices that the error message pointed to the FROM keyword as causing the error

    ergo...
    Yes, but if you read it without making assumptions, it purely states he receives an error on the line FROM _Content, doesn't specify what the error is, just the line it is thrown on (for all we know it could be a syntax issue -- since the query was obviously broken when posted). Nonetheless, I agree that the consolidation can at times make it harder to quickly resolve a problem if you don't know what Database the user is running.
    Be sure to congratulate Patche on earning July's Member of the Month
    Go ahead and blame me, I still won't lose any sleep over it
    My Blog | My Technical Notes

  11. #11
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,216
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by cpradio View Post
    ... harder to quickly resolve a problem if you don't know what Database the user is running.
    so what's wrong with asking, when necessary?

    many query problems can be resolved with sql that is so "vanilla" that it will work in any database

    UPDATE is ~not~ in that category

    neither are date functions, etc.

    so if it matters, and the original poster hasn't declared which database system is being used, i always ask first

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  12. #12
    Hosting Team Leader silver trophybronze trophy
    cpradio's Avatar
    Join Date
    Jun 2002
    Location
    Ohio
    Posts
    5,069
    Mentioned
    152 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    so what's wrong with asking, when necessary?

    many query problems can be resolved with sql that is so "vanilla" that it will work in any database

    UPDATE is ~not~ in that category

    neither are date functions, etc.

    so if it matters, and the original poster hasn't declared which database system is being used, i always ask first

    Off Topic:

    I agree, you can always ask, but sometimes being proactive to a problem that is an outcome of consolidation might be a better approach. Asking does typically solve the problem, but I'd like to explore ways of not having to do that (if one exists). Also, I can think of MANY differences just between MySQL and SQL Server, let alone when you add Oracle or Postgress to the mix.
    Be sure to congratulate Patche on earning July's Member of the Month
    Go ahead and blame me, I still won't lose any sleep over it
    My Blog | My Technical Notes


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •