Support for Drupal 7 is ending on 5 January 2025—it’s time to migrate to Drupal 10! Learn about the many benefits of Drupal 10 and find migration tools in our resource center.
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.
Comment | File | Size | Author |
---|---|---|---|
#10 | multi_delete-d6.patch | 2.51 KB | Island Usurper |
#10 | multi_delete-d5.patch | 2.38 KB | Island Usurper |
Comments
Comment #1
Island Usurper CreditAttribution: Island Usurper commentedA 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.
Comment #2
rszrama CreditAttribution: rszrama commentedComment #3
Anonymous (not verified) CreditAttribution: Anonymous commentedYou'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.
Comment #4
Island Usurper CreditAttribution: Island Usurper commentedFrom the Postgres DELETE documentation:
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.
Comment #5
ccurvey CreditAttribution: ccurvey commentedThis 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);
Comment #6
ccurvey CreditAttribution: ccurvey commentedComment #7
Island Usurper CreditAttribution: Island Usurper commentedCan I use the deleted table's alias in the subquery?
Comment #8
Island Usurper CreditAttribution: Island Usurper commentedActually, that syntax doesn't seem to work at all.
Comment #9
ccurvey CreditAttribution: ccurvey commentedNope, you can't alias the table you're deleting from ... but you can use the full name.
I think that should do it.
Comment #10
Island Usurper CreditAttribution: Island Usurper commentedNot 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.
Comment #11
deining CreditAttribution: deining commentedI can confirm that multi_delete-d6.patch works with ubercart 6.x-2.0-beta3 on PostgreSQL.
Comment #12
Island Usurper CreditAttribution: Island Usurper commentedGood enough for me. Committed multi_delete-d6.patch.
Anybody using Drupal 5, Ubercart, and Postgres?
Comment #13
deining CreditAttribution: deining commentedI can also confirm that multi_delete-d5.patch works with ubercart 5.x-1.7 on PostgreSQL 8.3.
Comment #14
Island Usurper CreditAttribution: Island Usurper commentedExcellent!