UPDATE queries should not use aliases

This will need to be fixed in HEAD first (D7-dev), then backported. Also promoted to the PostgreSQL surge (see #337146: Remove PostgreSQL driver from core for more context).

In Drupal 7, we still have those:

./modules/system/system.install: db_query('UPDATE {term_node} t SET vid = (SELECT vid FROM {node} n WHERE t.nid = n.nid)');
./modules/system/system.install: $ret[] = update_sql("UPDATE {poll_votes} v SET chid = (SELECT chid FROM {poll_choices} c WHERE v.chorder = c.chorder AND v.nid = c.nid)");
./modules/taxonomy/taxonomy.admin.inc: db_query('UPDATE {term_data} t SET weight = 0 WHERE vid = :vid', array(':vid' => $form_state['values']['vid']));

Let's fix them and move that issue quickly to D6.

Patch attached that removes the aliases in the 3 queries mentioned in #4.

Not all tests pass (due to other issues), but the number of fails doesn't change pre/post patch...we'll see how the testbot likes this.

Looks like there is an extra curly brace in the query: {poll_choices}}.chorder

+ $ret[] = update_sql("UPDATE {poll_votes} SET chid = (SELECT chid FROM {poll_choices} WHERE {poll_vodes}.chorder = {poll_choices}}.chorder AND {poll_votes}.nid = {poll_choices}.nid)");

Extra curly brace removed. Thanks.

-  db_query('UPDATE {term_node} t SET vid = (SELECT vid FROM {node} n WHERE t.nid = n.nid)');
+  db_query('UPDATE {term_node} SET vid = (SELECT vid FROM {node} WHERE {term_node}.nid = {node}.nid)');
   return $ret;
 }

I'm pretty sure we can keep the alias on {node}.

-    $ret[] = update_sql("UPDATE {poll_votes} v SET chid = (SELECT chid FROM {poll_choices} c WHERE v.chorder = c.chorder AND v.nid = c.nid)");
+    $ret[] = update_sql("UPDATE {poll_votes} SET chid = (SELECT chid FROM {poll_choices} WHERE {poll_vodes}.chorder = {poll_choices}.chorder AND {poll_votes}.nid = {poll_choices}.nid)");

{poll_vodes}? Also please keep the alias on {poll_choices}.

vodes...oops.

Fixes attached.

Perfect. Many thanks Jonathan.

Committed to CVS HEAD. Thanks.

Moving to 6.x to be ported.

It looks like the patch in #348448: Always report E_STRICT errors was accidentially committed together with the fix for this issue:
http://drupal.org/cvs?commit=159769

The patch in #348448 still applies using patch -R.

We are now working on D6. There are potentially more queries to convert, it's not a simple backport.

In fact, I only see two of them:

./modules/system/system.install: db_query('UPDATE {term_node} t SET vid = (SELECT vid FROM {node} n WHERE t.nid = n.nid)');
./modules/taxonomy/taxonomy.admin.inc: db_query('UPDATE {term_data} t SET weight = 0 WHERE vid = %d', $form_state['values']['vid']);

UPDATE \{\w\} \w SET

only one place

I couldn't find more than that single occurrence either.

Looks good, thanks, committed.