Select From One Table, Update Another

[Mcod]

Hello,

 

I am looking for a solution to this:

 

1.) I have a table called font where each font has a unique id called id

2.) I have a table called font_categories_join where each font id (above) has a categoryid

 

What I am trying to do is getting the categoryid into my font table, basically something like

 

updating fontcat (from font table) and setting the fontcat to the value that is in my categoryId inside the font_categories_join table

 

Detailed example:

 

table font contains this:

 

id fontName fontcat

1 some name 0

 

table font_categories_join contains:

fontId categoryId

1 12

 

so what I am trying to do via phpmyadmin is updating the fontcat in the font table from 0 to the correct category 12

 

Your help would be appreciated

[Barand]

UPDATE font f
INNER JOIN font_categories_join c
ON f.id = c.fontid
SET f.fontcat = c.categoryid

Edited September 22, 2012 by Barand

[Christian F.]

I must confess that I'm curious as to why you'd want to duplicate data like this? I don't see any specific advantage of this, but only a disadvantage as you've just created a possible data inconsistency point.

What happens if the category ID in the font table no longer matches the category ID and font ID combination in the font_categories table?

 

Not to mention you just invalidated the many-to-many relation you apparently were using prior to this.

[Mcod]

@Barand, thank you for your solution - it worked really great

 

@Christian F. The reason is saving queries - Categories will not change and this way I can get all details with one simple query without the need to do additional queries on another table. This sometimes matters if you run a large site and you have to watch how many queries you do. I understand what you are saying, but in my case this is the better solution.

 

Thanks again

[Barand]

What happens if the category ID in the font table no longer matches the category ID and font ID combination in the font_categories table?

 

Not to mention you just invalidated the many-to-many relation you apparently were using prior to this.

 

My assumption was that is 1 to many relationship and the join table has been found to be redundant

[Christian F.]

Mcod: Are you using JOINs, or running queries inside loops? If the latter, then the solution you've opted for in this thread is not the solution, but a poor workaround. If the former, you might want to be looking at caching your results instead. Considering the amount of traffic you must be getting, and the correct (first step) solution to that particular issue is caching.

 

Now, if Barand's assumption is correct, and that this is really a one-to-many relation then what you've done here is correct. Though, traffic loads really don't have any relevance on the correctness of the fix in this case.

 

Barand: Yep, figured so. Though, I don't like to assume, thus I asked.

[Barand]

Christian: I'm beginning to think we'll never know the answer to that one

[Christian F.]

Yeah... Me too. Shame really, but what can we do.

I know I won't lose any sleep over it though.