Copy Data from exp_channel_titles Based on entry_id

Question

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

Answer 1

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