Foreach in SQL?

Question

I'm not quite sure how to do this in SQL. Here it is in pseudocode:

1. Take the list of nodes with content type X.
2. For each node, take the value of field Y.
3. Insert into term_nodes VALUES ((tid that corresponds to Y), 4, (nid of node X))

The (tid that corresponds to Y) is given by

SELECT `tid` FROM `term_data` WHERE `name` = Y

(I'm trying to assign taxonomy in Drupal 6).

How can I do this?

Answer 1

You don't really want to do something like a foreach. Don't think of SQL as procedural, like most code (where you do one thing, then a second, and so on). You need to think of it as set based, where do you something to large chunks that meet certain requirements. Something like:

INSERT INTO term_nodes (tid, x, nid) -- these are the field names
<subquery that selects all the data>

The subquery should just select all the data you want to insert, perhaps something like:

SELECT nodeId, 4, termId FROM nodes WHERE contentType = X

So putting it all together, you get:

INSERT INTO term_nodes (tid, x, nid)
SELECT nodeId, 4, termId FROM nodes WHERE contentType = X

No need to try to run through each element in the subquery and insert them one at a time, just do it all at once.

Here is a good article I found about Procedural versus Set-Based SQL that can help you understand the concept a little better.