On Aug 21, 2012, at 6:32 PM, Warwick Prince wrote:
> Hi
>
> I have created a simple update like this on a PG database via PG8000;
>
> table=Table('invoice_line_items', meta, autoload=True)
> query = table.update()
> query = query.where(and_(eq(table.c.InvBook, 'SC'), eq(table.c.InvNum,
> 12862), eq(table.c.InvLine, 1)))
> query = query.values(**data)
> query.execute()
>
> I'm getting this error, and looking at the SQL (from the error message), I
> can't see what is wrong or why PG is complaining.
>
> ('ERROR', '42712', 'table name "invoice_line_items" specified more than
> once') u'UPDATE invoice_line_items SET "DocType"=%s, "LineType"=%s,
> "Complete"=%s, "Group"=%s, "Product"=%s, "SerialNumber"=%s, "Description"=%s,
> "Warehouse"=%s, "UnitCode"=%s, "UnitQty"=%s, "Supplier"=%s, "Active"=%s,
> "Customer"=%s, "BillTo"=%s, "Date"=%s, "ConsignmentFlag"=%s, "TaxFlag"=%s,
> "HiddenFlag"=%s, "JoinFlag"=%s, "OrderQty"=%s, "ReserveQty"=%s,
> "BackorderQty"=%s, "InvoiceQty"=%s, "ShippedQty"=%s, "Currency"=%s,
> "CostEach"=%s, "SellEach"=%s, "TotalEx"=%s, "Total"=%s, "InputTax"=%s,
> "OutputTax"=%s, "TaxTotal"=%s, "BudgetCostEx"=%s, "BudgetCostInc"=%s,
> "Weight"=%s, "GLRevenue"=%s, "GLCost"=%s, "GLCostFrom"=%s, "PriceLevel"=%s,
> "OrderType"=%s, "StatusCode"=%s, "ReasonCode"=%s, "LotNumber"=%s, "GST"=%s,
> "BookingID"=%s, "StampDuty"=%s, "Insurance"=%s, "PriceSource"=%s,
> "SalesRep"=%s, "ETADate"=%s, "CostCentreRevenue"=%s, "CostCentreCost"=%s,
> "CostCentreCostFrom"=%s, "RebateRule"=%s, "CustOrderUnitCode"=%s,
> "CustOrderQty"=%s, "FreightMode"=%s, "FreightExEach"=%s,
> "FreightExApplied"=%s, "GLFreight"=%s, "BundleGroup"=%s, "BundleOrderQty"=%s,
> "BundleComponentQty"=%s, "MinSell"=%s, "DiscTaxApplyFlag"=%s, "yearWeek"=%s,
> "yearMonth"=%s, "yearQuarter"=%s, colour=%s, "feeCode"=%s, "feeTotalEX"=%s
> FROM invoice_line_items WHERE invoice_line_items."InvBook" = %s AND
> invoice_line_items."InvNum" = %s AND invoice_line_items."InvLine" = %s'
> (u'C', u'P', u'Y', u'FIL', u'OBS3E', u'', u'ORGANIC SUPREME SKIN ON', u'S01',
> u'KG', 1.0, u'MANDJ', u'Y', u'S2550', u'S2550', datetime.date(2007, 6, 5),
> u'N', u'N', u'N', u'N', -2.4, 0.0, 0.0, 0.0, 0.0, u'AUD', 0.0, 19.5, -46.8,
> -46.8, u'', u'N', 0.0, 0.0, 0.0, -2.4, u'101-30010-000', u'101-10530-000',
> u'101-10530-000', u'L', u'', u'', u'', u'', 0.0, 0, 0.0, 0.0, u'L', u'SAM',
> None, u'', u'', u'', u'', u'KG', -2.4, u'', 0.0, 0.0, u'', u'', 0.0, 0.0,
> 0.0, u'', u'2007-22', u'2007-06', u'2007-02', None, None, None, u'SC', 12862,
> 1)
so there's a FROM in there, which is a PG syntax we've recently started
supporting known as UPDATE..FROM. It's supposed to be used when there are
additional tables in the UPDATE statement other than the target table. But
here we have UPDATE invoice_line_items SET ... FROM invoice_line_items WHERE
... which is wrong. What would cause that here would be if you have two
separate Table objects both called "invoice_line_items" - one is mentioned as
the subject via table.update(), and the other would be embedded in the WHERE
clause somehow. The code you've illustrated doesn't show any way this might
be happening. Something is different in the actual code.
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to sqlalchemy@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
