SitePoint Sponsor

User Tag List

Results 1 to 8 of 8
  1. #1
    SitePoint Wizard bronze trophy cydewaze's Avatar
    Join Date
    Jan 2006
    Location
    Merry Land, USA
    Posts
    1,096
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)

    Updating one table with value from another table

    I have a database with two tables. One is a list of our web pages, and the other is a list of topics for our website. This database was cobbled together over a number of years, and it started off serving a totally different purpose than it serves now.

    So we basically have this.

    [web page table]
    page_id (autonumber)
    page_topic (text)
    ...


    [topics table]
    topic_id (autonumber)
    topic_name (text)
    ...


    Right now, the tables are joined by the two text columns (page_topic = topic_name) which is not ideal. I've created a new topic ID column in the web page table that I want to use instead, but I need to populate that table with the ID from the topics table.

    I've tried all three of these to no avail:

    Code SQL:
    UPDATE webpages
    SET webpages.topic_id = ( SELECT topics.topic_id
    			FROM topics
    			WHERE topics.topic_name = webpages.page_topic )
    WHERE webpages.page_topic = topics.topic_name

    Code SQL:
    UPDATE webpages
    SET topic_id = topics.topic_id
    FROM webpages
    INNER JOIN topics
    	ON webpages.page_topic = topics.topic_name
    WHERE page_topic = topic_name

    Code SQL:
    UPDATE webpages
    SET webpages.topic_id = topics.topic_id
    FROM webpages, topics
    WHERE webpages.page_topic = topics.topic_name

    Either I'm on the wrong track, or this is an Access idiosyncrasy. Anyone know which?
    <cfset myblog = "http://cydewaze.org/">

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,263
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by cydewaze View Post
    Either I'm on the wrong track, or this is an Access idiosyncrasy. Anyone know which?
    i do
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,263
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    okay, that wasn't fair, was it

    try this, see if you can make sense out of it (i couldn't) --
    http://office.microsoft.com/en-ca/ac...005188710.aspx
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,263
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    and if that doesn't work, try this, which is your first query with a different WHERE clause
    Code:
    UPDATE webpages
       SET webpages.topic_id = 
           ( SELECT topics.topic_id
               FROM topics
              WHERE topics.topic_name = webpages.page_topic )
     WHERE EXISTS 
           ( SELECT topics.topic_id
               FROM topics
              WHERE topics.topic_name = webpages.page_topic )
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Wizard bronze trophy cydewaze's Avatar
    Join Date
    Jan 2006
    Location
    Merry Land, USA
    Posts
    1,096
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)
    Thanks rudy. That gives me the all too familiar "[Macromedia][SequeLink JDBC Driver][ODBC Socket][Microsoft][ODBC Microsoft Access Driver] Operation must use an updateable query. " error.

    I might just have to query the topics, and stick an update query in the <cfoutput> tags and make a loop.
    <cfset myblog = "http://cydewaze.org/">

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,263
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    presumably you are only going to do this once, so any way to get it done is fine

    what about that microsoft article, did that make sense?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    SitePoint Wizard bronze trophy cydewaze's Avatar
    Join Date
    Jan 2006
    Location
    Merry Land, USA
    Posts
    1,096
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    what about that microsoft article, did that make sense?
    Do they ever?

    I think that article is how to do it in the Access desktop app, which I have no clue whatsoever how to use.

    The previous method I mentioned worked though. Thanks for the second pair of eyes!
    <cfset myblog = "http://cydewaze.org/">

  8. #8
    SitePoint Member
    Join Date
    May 2011
    Posts
    17
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I tried the script and it really works, thanks for sharing.


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
  •