0

Before I run db_delete, I'm trying to run db_select to test that I am getting all duplicate rows, based on a specific column value.

db_select('node_revision', 'n')
    ->where('n.nid NOT IN SELECT MIN(n.nid)')
    ->groupBy('hash')
    ->execute()
    ->fetchAll();

Basically, delete (select for the test) all rows with the same hash value, except for the first one, is my goal.

I'm basing it on this page:

http://blog.sqlauthority.com/2007/03/01/sql-server-delete-duplicate-records-rows/

But for some reason I get the following error:

PDOException: SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'FROM node_revision n WHERE (n.nid NOT IN SELECT MIN(nid)) GROUP BY hash' at line 1: SELECT FROM {node_revision} n WHERE (n.nid NOT IN SELECT MIN(nid)) GROUP BY hash; Array ( ) in include() 

What am I doing wrong? Why is ; array ( ) there?

1

At the moment your query is:

SELECT 
FROM 
{node_revision} n
WHERE  (n.nid NOT IN SELECT MIN(n.nid)) 
GROUP BY hash

You need to add some fields...

db_select('node_revision', 'n')
  ->fields('n', array('vid'))
  ->where('n.nid NOT IN SELECT MIN(n.nid)')
  ->groupBy('hash')
  ->execute()
  ->fetchAll();
  • I don't actually need vid though. – Jack Ryan Feb 24 '14 at 16:56
  • OK, so pick the column that you do need. – Clive Feb 24 '14 at 16:56
  • I'm only trying to cull everything with a duplicate hash value, that has the same nid. Even when I try to grab fields, I'm getting an error. db_select('node_revision', 'n') ->fields('n', array('nid', 'hash')) ->where('n.nid NOT IN SELECT MIN(n.nid)') ->groupBy('hash') ->execute() ->fetchAll(); – Jack Ryan Feb 24 '14 at 16:58
  • PDOException: SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'SELECT MIN(n.nid)) GROUP BY hash' at line 1: SELECT n.nid AS nid, n.hash AS hash FROM {node_revision} n WHERE (n.nid NOT IN SELECT MIN(n.nid)) GROUP BY hash; Array ( ) – Jack Ryan Feb 24 '14 at 16:59
  • 'Cull' would surely involve a DELETE query? A SELECT has to have fields, by definition. There are a few other things wrong with your query, I don't know what you're trying to accomplish so I can't really help. This is wrong: (n.nid NOT IN SELECT MIN(n.nid)), needs to be (n.nid NOT IN (SELECT MIN(n.nid))), and there's no hash column in node_revision, so you can't group on it. Write the SQL query that gets you the right data first, then worry about getting it into Drupal – Clive Feb 24 '14 at 17:03

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.