(SQL) How can I add values into a table based on the IDs of another table?

Question

I need to be able to add a set of values into a table every time a new user is added into my system.

When the user is added, I want to look into my 'Tags' table, and insert a new entry into the 'Tag_Score' table for each of the IDs in the 'Tags' table.

I tried the following based on something i found online but although the logic seems sound, it doesn't seem to be working

DECLARE @LoopVar INTEGER
SET @LoopVar = ( SELECT MIN(Tag_Score.T_ID) 
FROM Tags ) WHILE @LoopVar IS NOT NULL 
BEGIN 
INSERT INTO  `a3360218_DD`.`Tag_Score` (
`A_ID` ,
`T_ID` ,
`Score` ,
`Visits`
)
VALUES (
'" . $accountID . "', @LoopVar ,  '0',  '0'
)
SET @LoopVar = ( SELECT MIN(Tag_Score.T_ID) 
FROM TheTable
WHERE @LoopVar < T_ID ) 
END

The error given is:

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'WHILE @LoopVar IS NOT NULL 
BEGIN 
INSERT INTO  `a3360218_DD`.`Tag_Score` (

' at line 2

Thanks in advance

Matt

It would really help if you explained a bit more *in English* what it is you're trying to do, rather than posting broken code (although it's good you tried something). Also, please post table definitions of `Tags` and `Tag_Score` — Bohemian, Apr 04 '12 at 15:40
I am struggling to understand your question but it sounds like you need to look at [INSERT... SELECT](http://dev.mysql.com/doc/refman/5.5/en/insert-select.html) — user1191247, Apr 04 '12 at 15:43
Sorry i've had a pretty long day, just re-read this and you're both right, my english is terrible haha! This INSERT...SELECT stuff seems to be the right way though, i'd never heard of it before (kinda new to this all). Thanks a lot guys — Matthew Albone, Apr 04 '12 at 16:34

score 3 · Accepted Answer · answered Apr 04 '12 at 15:45

3

You haven't posted any table layouts, so I'm shooting in the dark here, but this kind of query should work for you:

INSERT INTO Tag_Score (A_ID, T_ID, Score, Visits)
SELECT A_ID, T_ID, 0,  0
FROM Tags
WHERE A_ID = $accountID;

Note the removal of all those unnecessary back-ticks (ie `) that are only necessary when you have a schema name that is a reserved word, and look how readable it is now!

Keep things simple - avoid complex SQL if you can.

answered Apr 04 '12 at 15:45

Bohemian

412,405
93
575
722

I think that might be `SELECT $accountID, T_ID, 0, 0` since A_ID is not in the `Tags` table. – Joachim Isaksson Apr 04 '12 at 15:50
@JoachimIsaksson How do you know? You might be right, but who would know - he hasn't posted schemas. – Bohemian Apr 04 '12 at 15:52
Turns out he was right, but I'll definitely not forget to put my table structures in if i need to ask another question. Thanks a lot Bohemian – Matthew Albone Apr 04 '12 at 16:38
You're welcome! And welcome to stackoverflow - hopefully you'll return often to answer as well as ask :) – Bohemian Apr 04 '12 at 16:43
I'm sure when I'm a lot better at all this stuff I'll enjoy helping out the newest load of youngsters indeed haha =D – Matthew Albone Apr 04 '12 at 16:59

score 0 · Answer 2 · answered Apr 04 '12 at 15:47

0

I think you might want CROSS JOIN, or maybe just a regular insert from...

insert into Tag_Score
select accountID, t.ID, 0, 0
from tags t
-- optional order by
-- order by t.ID

answered Apr 04 '12 at 15:47

Chris Gessler

22,727
7
57
83

(SQL) How can I add values into a table based on the IDs of another table?

2 Answers2