Updating one table with value from another table

Databases
1

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:

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

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

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?

2

i do :slight_smile:

3

okay, that wasn’t fair, was it :wink:

try this, see if you can make sense out of it (i couldn’t) –

4

and if that doesn’t work, try this, which is your first query with a different WHERE clause

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 )
5

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.

6

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?

7

Do they ever? :stuck_out_tongue:

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!

8

I tried the script and it really works, thanks for sharing.