1

I'm trying to copy some channel data from the exp_channel_titles table into Better Workflow's exp_ep_entry_drafts table. I want to copy the data by matching the entry_ids, which already exist in both tables. So the status for entry ID 42 in exp_channel_titles will go into the status column for entry ID 42 in exp_ep_entry_drafts.

I'm very much a MySQL n00b, but here's what I came up with:

INSERT INTO
  exp_ep_entry_drafts (
    status,
    url_title,
    expiration_date,
    entry_date
  )
SELECT 
  titles.status,
  titles.url_title,
  titles.expiration_date,
  titles.entry_date
FROM 
  exp_channel_titles titles
JOIN 
  exp_ep_entry_drafts
ON
  titles.entry_id = exp_ep_entry_drafts.entry_id

When I run that in the EE query form, I get this error:

Fatal error: Call to a member function num_rows() on a non-object in /path/to/the/system/expressionengine/controllers/cp/tools_data.php on line 482

2

I have no idea what the num_rows() thing is about -- that's not an SQL error, it's an EE error that says it's having trouble running $this->EE->db->query -- but in any case, the query above has several issues. It's an INSERT, which means it inserts new rows; what it sounds like you want is to update existing rows, which uses a (wait for it!) UPDATE query. It also joins to the table you're INSERTing into, which generally isn't allowed, so it may be what's causing the EE error.

Try this:

UPDATE 
    exp_ep_entry_drafts ed, exp_channel_titles t
SET
    ed.status = t.status,
    ed.url_title = t.url_title,
    ed.expiration_date = t.expiration_date,
    ed.entry_date = t.entry_date
WHERE
    ed.entry_id = t.entry_id
1
  • Duh! Clearly I had a case of making it way more complicated than it needed to be.
    – kmgdev
    Apr 11 '13 at 23:18

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.