[SQL] 'UPDATE OR INSERT' command

Is there a postgresql SQL idiom to perform an UPDATE, which becomes an INSERT if the primary key does not exist? I'm not sure I *should* use it in my application, I just want to know if it can be done. Thanks. ---(end of broadcast)--- TIP 2: you ca

[SQL] update table where rows are selected by inner join?

I have two tables orders and customerpaymentnote, which keep denormalized columns of the status in rows related by orderid. The column duplication is intentional, to ease end-user ad-hoc queries. I don't understand the UPDATE FROM clause at: http://www.postgresql.org/docs/7.4/static/sql-update.html

Re: [SQL] Trigger plpgsql function, how to test if OLD is set?

Tomasz Myrta wrote: > You can always check whether your trigger has been fired as insert or > update trigger. > DECLARE old_orderid integer; > BEGIN >if TG_OP=''UPDATE'' then > old_orderid=OLD.orderid; >else > old_orderid=-1; >end if; Thank you, that works well enough. I'

[SQL] Trigger plpgsql function, how to test if OLD is set?

I have a test I need to do in my trigger function to see if a standard set of shipmentcharges exists, if not I insert two rows. IF (SELECT COUNT(orderchargeid) FROM ordercharges WHERE orderid=NEW.orderid OR orderid=OLD.orderid)=0 THEN I added the "OR orderid=OLD.orderid" expression to handle th

Re: [SQL] Help on update that subselects other records in table, uses joins

Josh Berkus wrote: > You may only UPDATE one table at a time, you can't update a JOIN. So when > selecting from another table to filter or calculate your update, the form is: > UPDATE orderchanges > SET orderchargesbilled = {expression} > FROM orders > WHERE orders.orderid = ordercharges.orderid

[SQL] Help on update that subselects other records in table, uses joins

I need to adapt this an update statement to a general form that will iterate over multiple orderids for a given customerinvoiceid. My first concern is a form that will work for a given orderid, then an expanded version that will work on all orderids with a specific customerinvoiceid as a parameter.

[SQL] help on update subselect with joins

I need to adapt this an update statement to a general form that will iterate over multiple orderids for a given customerinvoiceid. My first concern is a form that will work for a given orderid, then an expanded version that will work on all orderids with a specific customerinvoiceid as a parameter.

[SQL] updating a field with a SUM from another table

My SQL is apparently a bit rusty, can anyone advise how to refactor this updatecharges query to work? I need to update a total-charges field in my orders table with the sum of the line-item charges in another table. The tables are related by the orderid column. Thanks for any help you can provide

[SQL] SQL select count(*) from "myTable" failed : Relation "mytable" does not exist

What do these errors mean? I get them on certain databases when trying to view table data in webmin's postgresql module. I don't think its Webmin specific. The databases in question are often exported from Access XP using pgAdmin 1.4.2 and the migration plugin (and imported using psql -f), but I al