Help with code...

[tthdoc]

Could use some help.  I am overlooking something here but can't figure it out.

 

I have a database with a table with 3 columns - id, total_value and total_votes.  I have a second table with 2 columns id and votes1.  What I need is the number total_value/total_votes to be inserted into the votes column, and making sure the id's match.  Here is the SQL statement I tried but get an error.

 

INSERT INTO joom_stories (votes1) SELECT (total_value/total_votes) FROM joom_ratings WHERE joom_stories.id=joom_ratings.id

 

I get this error..

#1054 - Unknown column 'joom_stories.id' in 'where clause'

 

Thanks,

Doc

[tomindo]

try this

INSERT INTO joom_stories (votes1) SELECT (total_value/total_votes) FROM joom_ratings,joom_stories WHERE joom_stories.id=joom_ratings.id

 

 

[tthdoc]

Ok, now for another problem.  I tried your solution as well as:

 

INSERT INTO joom_stories (votes1) SELECT (total_value/total_votes) FROM joom_ratings,joom_stories WHERE id=joom_ratings.id

 

Both ways seemed to do the same thing.  When I went to the tables, it added a new row for each transfer.  So instead of having, say 50 rows in stories and 50 rows in ratings, I end up with 100 rows in stories (and no value in votes 1 of the existing 50 rows changed) and still 50 in ratings.  So instead of matching the id's and replacing the votes value, it just keeps adding more id numbers.

 

Doc

[tthdoc]

OK finally figured in out.  Insert will insert rows (duh).  I want to update.  So for anyone who wants to do the same thing, here is what I used:

 

UPDATE joom_stories s

SET votes1 = (SELECT (total_value/total_votes) FROM joom_ratings r WHERE s.id=r.id)

 

Doc