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

If you can use regular SQL, could you not use an INSERT/SELECT statement?

INSERT INTO term_nodes(fld, fld2, fld3)
  SELECT tid, nodeid, 4 FROM term_data WHERE ... ?

Answer 2

INSERT INTO `term_nodes` (fld1, fld2, fld3) 
      SELECT tn.`tid`, 4, n.`nid`
      FROM `nodes` n
      INNER JOIN `term_nodes` tn ON tn.`name`=n.`Y`
      WHERE n.`ContentType`= X

Answer 3

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.