Received this error when deleting an attribute from within a product node. I am using Drupal 5.5 with PostgreSQL.

    * warning: pg_query() [function.pg-query]: Query failed: ERROR: syntax error at or near "po" LINE 1: DELETE po FROM uc_product_options AS po LEFT JOIN uc_attribu... ^ in /usr/local/apache2/htdocs/drupal/includes/database.pgsql.inc on line 125.
    * user warning: query: DELETE po FROM uc_product_options AS po LEFT JOIN uc_attribute_options AS ao ON po.oid = ao.oid WHERE po.nid = 230 AND ao.aid IN (1) in /usr/local/apache2/htdocs/drupal/includes/database.pgsql.inc on line 144.
Support from Acquia helps fund testing for Drupal Acquia logo

Comments

Island Usurper’s picture

A quick search hasn't shown me what to do in Postgres for handling multiple tables in a DELETE statement. That's how MySQL wants it, but it's probably not the SQL Standard way to do it.

rszrama’s picture

Title: ERROR: syntax error at or near "po" LINE 1: DELETE po FROM uc_product_options » PostgreSQL error when deleting an attribute within a product node
Status: Active » Postponed (maintainer needs more info)
Anonymous’s picture

You're syntax is wrong.

Consider the first part of the query as stated in the error message: DELETE po FROM uc_product_options AS po ...

This saying to delete table "po" which is an alias for the table uc_product_options -- so basically you're telling it to delete the table uc_products_options, in a roundabout way.

If you're looking to delete specific rows, what you want to say is: DELETE FROM uc_product_options AS po ...

Here's the URL to the PostgreSQL 8.3 documentation for DELETE: http://www.postgresql.org/docs/8.3/interactive/sql-delete.html

And here's the URL for MySQL 5.0: http://dev.mysql.com/doc/refman/5.0/en/delete.html

Thanks,
Curtis.

Island Usurper’s picture

From the Postgres DELETE documentation:

Do not repeat the target table in the usinglist, unless you wish to set up a self-join.

This sounds like a different use of the USING clause than what MySQL does in its DELETE statements. I'm not sure how I can reconcile using the target table in USING for MySQL and not using it for Postgres.

Might be that I should just run two queries and make it simpler.

ccurvey’s picture

This is the syntax that you want.

db_query("DELETE FROM $opt_table WHERE EXISTS (
SELECT * FROM {uc_attribute_options} AS ao
WHERE $opt_table.oid = ao.oid
AND $opt_table.$id = $sql_type
AND ao.aid IN (%s))", $values);

ccurvey’s picture

Status: Postponed (maintainer needs more info) » Active
Island Usurper’s picture

Status: Active » Postponed (maintainer needs more info)

Can I use the deleted table's alias in the subquery?

db_query("DELETE FROM {uc_product_options} AS po WHERE EXISTS (
  SELECT * FROM {uc_attribute_options} AS ao
  WHERE po.oid = ao.oid
    AND po.$id = $sql_type
    AND ao.aid IN (%s))", $values);
Island Usurper’s picture

Actually, that syntax doesn't seem to work at all.

ccurvey’s picture

Status: Postponed (maintainer needs more info) » Active

Nope, you can't alias the table you're deleting from ... but you can use the full name.

db_query("DELETE FROM {uc_product_options} WHERE EXISTS (
  SELECT * FROM {uc_attribute_options} AS ao
  WHERE {uc_product_options}.oid = ao.oid
  AND ao.aid IN (%s))
  AND {uc_product_options}.$id = $sql_type", $values);

I think that should do it.

Island Usurper’s picture

Version: 5.x-1.0-beta5 » 6.x-2.x-dev
Assigned: Unassigned » Island Usurper
Status: Active » Needs review
FileSize
2.38 KB
2.51 KB

Not using aliases makes me grumpy, but that does work as far as I can tell. Somebody make sure these patches work for both Drupal 5 and 6 on Postgres.

deining’s picture

I can confirm that multi_delete-d6.patch works with ubercart 6.x-2.0-beta3 on PostgreSQL.

Island Usurper’s picture

Good enough for me. Committed multi_delete-d6.patch.

Anybody using Drupal 5, Ubercart, and Postgres?

deining’s picture

I can also confirm that multi_delete-d5.patch works with ubercart 5.x-1.7 on PostgreSQL 8.3.

Island Usurper’s picture

Status: Needs review » Fixed

Excellent!

Status: Fixed » Closed (fixed)

Automatically closed -- issue fixed for 2 weeks with no activity.