By sgtaw on

Here's the problem.

I need to change the value of one column (PARENT) in one table (BOOK).

However, to get the proper rows for the change I have to join BOOK to NODE for the rows that need to be changed based on a UID that doesn't exist in BOOK.

So the syntax to show the columns is this:

SELECT *
FROM `test_drpl1`.`node` , `test_drpl1`.`book`
WHERE `node`. `uid` =8
AND `node`.`nid` = `book` .`nid`

I know it is some sort of UPDATE command, but can't figure out the exact.

One of you geniuses should know!

Thanks in advance!

Ed

⋅ Categories: Drupal 5.x

Comments

nancydru’s picture

nancydru
she/her/hers
Primary language English
Location Boston
commented

If there's not a lot to do, why not just do it manually under phpMyAdmin? Since you are showing your SQL with back ticks, I'm assuming you're doing this in phpMyAdmin.

Looking at the book table, I would suggest a JOIN on node_revisions, rather that node. Book appears to be keyed on the version.

SELECT * FROM `book` b INNER JOIN `node_revisions` nr ON b.vid=nr.vid AND b.nid=nr.nid WHERE nr.uid=8

Nancy W.
Drupal Cookbook (for New Drupallers)
Adding Hidden Design or How To notes in your database

NancyDru

sgtaw’s picture

sgtaw commented

Hi Nancy!

Thanks for the code! It worked great. I wish I could do a manual change but there are too many records.

Now another question, if I may....

I now need to update a column in the 'book' table with a constant.

So I made the code like this based on what you had given me....

update b
set parent = 35
FROM `book` b
INNER JOIN `node_revisions` nr ON b.vid = nr.vid
AND b.nid = nr.nid
WHERE nr.uid =8

Basically, I wanted to update the column 'parent' with the 35

Thanks in advance!

By the way, your drupal cookbook is awesome!

Ed Aw
~*
Bowling Tips for Better Bowling
Church Planting

nancydru’s picture

nancydru
she/her/hers
Primary language English
Location Boston
commented

I've only been doing SQL for 6 months or so and largely picking it up by doing. I don't know if an UPDATE can be done with a JOIN. I would code a SELECT and then write a loop that updated individual rows from those results. Something like this (in a Drupal page):

  $results = dq_query('SELECT * FROM {book} b INNER JOIN {node_revisions} nr ON b.vid=nr.vid AND b.nid=nr.nid WHERE nr.uid=8');
  while ($page = db_fetch_array($results)) {
    db_query('UPDATE SET parent=35 FROM {book} WHERE nid=%d AND vid=%d', $page['nid'], $page['vid']);
  }

This is untested.

Nancy W.
Drupal Cookbook (for New Drupallers)
Adding Hidden Design or How To notes in your database

NancyDru