CODEWITHSUNDEEP
mysql
Adam
Adam

Reputation: 20912

MySQL Select from 1 Table, Insert into Another using Where across different databases

I have a select shown below that brings back the correct count per "sender_userid".

I want to insert this into a table in another database where there is a matching column "userid".

I'm not sure how to get the WHERE clause to work when used across database.

I've tried database.table.column but this appears wrong.

Is this possible? thx

Upvotes: 0

Views: 39

Answers (1)

Mike Furlender
Mike Furlender

Reputation: 4019

WHERE statements must come before ORDER BY and GROUP BY statements. Also you should use the ON operator. Try this:

 INSERT INTO dating_users.statistics (messages_sent)
 SELECT COUNT(pid) FROM dating_messages.messages M
 JOIN dating_users.statistics S
 ON (S.userid = M.sender_userid) 
 GROUP BY sender_userid ORDER BY sender_userid ASC;

Edit: sorry I didn't realize that you were missing your actual JOIN statement. Just because you are INSERTing into a table doesn't make any data accessible from that table. You still need to JOIN to it.

Upvotes: 1

Related Questions