Geek Answers Handbook

MySQL Unkown column in the where section

I have two databases. One of them is called INFOwith three tables ( Stories, Comments, Replies)

Stories has the following fields

+--------------+----------------+------+-----+---------------------+-----------------------------+
| Field        | Type           | Null | Key | Default             | Extra                       |
+--------------+----------------+------+-----+---------------------+-----------------------------+
| storyID      | int(11)        | NO   | PRI | NULL                |                             |
| originalURL  | varchar(500)   | YES  |     | NULL                |                             |
| originalDate | timestamp      | NO   |     | CURRENT_TIMESTAMP   | on update CURRENT_TIMESTAMP |
| numDiggs     | int(11)        | YES  |     | NULL                |                             |
| numComments  | int(11)        | YES  |     | NULL                |                             |
| diggURL      | varchar(500)   | YES  |     | NULL                |                             |
| rating       | varchar(50)    | YES  |     | NULL                |                             |
| title        | varchar(200)   | YES  |     | NULL                |                             |
| summary      | varchar(10000) | YES  |     | NULL                |                             |
| uploaderID   | varchar(50)    | YES  |     | NULL                |                             |
| imageURL     | varchar(500)   | YES  |     | NULL                |                             |
| category1    | varchar(50)    | YES  |     | NULL                |                             |
| category2    | varchar(50)    | YES  |     | NULL                |                             |
| uploadDate   | timestamp      | NO   |     | 0000-00-00 00:00:00 |                             |
| num          | int(11)        | YES  |     | NULL                |                             |
+--------------+----------------+------+-----+---------------------+-----------------------------+

Another database is called Datawith one table ( User). Fields shown below:

+-------------------+-------------+------+-----+---------+-------+
| Field             | Type        | Null | Key | Default | Extra |
+-------------------+-------------+------+-----+---------+-------+
| userID            | varchar(50) | NO   | PRI | NULL    |       |
| numStories        | int(11)     | YES  |     | NULL    |       |
| numComments       | int(11)     | YES  |     | NULL    |       |
| numReplies        | int(11)     | YES  |     | NULL    |       |
| numStoryDiggs     | int(11)     | YES  |     | NULL    |       |
| numCommentReplies | int(11)     | YES  |     | NULL    |       |
| numReplyDiggs     | int(11)     | YES  |     | NULL    |       |
| numStoryComments  | int(11)     | YES  |     | NULL    |       |
| numStoryReplies   | int(11)     | YES  |     | NULL    |       |
+-------------------+-------------+------+-----+---------+-------+

User.userIDcontains thousands of unique names. All other fields are currently NULL. The names in User.userIDcorrespond to the names in Stories.uploaderID.

I need, for each userIDin User, to count the number of stories loaded from (e.g. num) Storiesfor the corresponding name, and paste this value into User.numStories.

The query I came across (which causes an error):

INSERT INTO DATA.User(numStories) 
SELECT count(num) 
FROM INFO.Stories 
WHERE INFO.Stories.uploaderID=DATA.User.userID;

Error executing this request

 Unknown column 'DATA.User.userID' in 'where clause'

Sorry if this is poorly explained. I will try and explain if necessary.

+4
1

User, . , insert , update:

UPDATE DATA.User u
JOIN   (SELECT   uploaderID, SUM(num) AS sumNum
        FROM     INFO.Stories
        GROUP BY uploadedID) i ON i.uploaderID = u.userID
SET    numStories = sumNum

EDIT:
, . num Stories uploaderId. updates numStories User .

+1

More articles:


All Articles