[SQL] Problem with FOR UPDATE
Hi All, I have a query in which I want to SELECT FOR UPDATE same rows but only from one table. Firs I try just with SELECT FOR UPDATE but I receive an error because of the LEFT JOIN - ERROR: SELECT FOR UPDATE/SHARE cannot be applied to the nullable side of an outer join. So I decide to use SELECT FOR UPDATE OF table name but I then receive the error you can see. Can anyone help me with this query? Thanks in advance. Regards, Kaloyan Iliev rsr=# SELECT rsr-#DD.* rsr-# ( SELECT sum(-amount * saldo_sign(credit)) rsr(# FROM acc_debts ACD1 rsr(# WHERE ACD1.debtid = DD.debtid ) AS saldo, rsr-# C.custid, rsr-# S.descr_bg rsr-#FROM debts_desc DD LEFT JOIN config C ON (DD.conf_id = C.id), rsr-# acc_debts AD, rsr-# acc_clients AC, rsr-# services S rsr-#WHERE DD.debtid = AD.debtid rsr-# AND DD.closed AND NOT DD.paid AND DD.has_invoice AND DD.active AND DD.has_proform AND NOT DD.storned rsr-# AND AD.transact_no = AC.transact_no rsr-# AND AC.ino = 45 rsr-#FOR UPDATE OF debts_desc; ERROR: relation debts_desc in FOR UPDATE/SHARE clause not found in FROM clause rsr=# select version(); version PostgreSQL 8.1.4 on i386-portbld-freebsd6.1, compiled by GCC cc (GCC) 3.4.4 [FreeBSD] 20050518 (1 row) ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [SQL] Problem with FOR UPDATE
Kaloyan Iliev [EMAIL PROTECTED] writes: rsr=# SELECT ... rsr-#FROM debts_desc DD LEFT JOIN config C ON (DD.conf_id = C.id), ... rsr-#FOR UPDATE OF debts_desc; ERROR: relation debts_desc in FOR UPDATE/SHARE clause not found in FROM clause Use the alias, ie, DD. Remember that an alias hides the real name of that table for all purposes in the current query. regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Problem with FOR UPDATE
On Thu, 7 Sep 2006, Kaloyan Iliev wrote: Hi All, I have a query in which I want to SELECT FOR UPDATE same rows but only from one table. Firs I try just with SELECT FOR UPDATE but I receive an error because of the LEFT JOIN - ERROR: SELECT FOR UPDATE/SHARE cannot be applied to the nullable side of an outer join. So I decide to use SELECT FOR UPDATE OF table name but I then receive the error you can see. I think you'd want to use DD not debts_desc as you've renamed the from list entry. Can anyone help me with this query? Thanks in advance. Regards, Kaloyan Iliev rsr=# SELECT rsr-#DD.* rsr-# ( SELECT sum(-amount * saldo_sign(credit)) rsr(# FROM acc_debts ACD1 rsr(# WHERE ACD1.debtid = DD.debtid ) AS saldo, rsr-# C.custid, rsr-# S.descr_bg rsr-#FROM debts_desc DD LEFT JOIN config C ON (DD.conf_id = C.id), rsr-# acc_debts AD, rsr-# acc_clients AC, rsr-# services S rsr-#WHERE DD.debtid = AD.debtid rsr-# AND DD.closed AND NOT DD.paid AND DD.has_invoice AND DD.active AND DD.has_proform AND NOT DD.storned rsr-# AND AD.transact_no = AC.transact_no rsr-# AND AC.ino = 45 rsr-#FOR UPDATE OF debts_desc; ERROR: relation debts_desc in FOR UPDATE/SHARE clause not found in FROM clause ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[SQL] Problem with simple update query
Hi All, Would appreciatesomehelpwith something that I think shouldbe really easy but I've been tearing my hair outwith all day. I want to execute a querywhichadds a new row into a tableor updates an existing row ifthe id alreadyexists. Something like thequery I would liketo execute isshown below... (I am trying to useNpgsql in C#incidently). BEGIN IF :ID 0 THENINSERT INTOmyTable DEFAULT VALUES;:ID = curval(properties); END UPDATEmyTable SETtype = :TYPE,address_id = :ADDRESS_ID,address_line1 = :ADDRESS_LINE1,address_line2 = :ADDRESS_LINE2,address_line3 = :ADDRESS_LINE3,town = :TOWN, county = :COUNTY,WHERE id = :ID; COMMIT With trying variations of the above, the problems seem to be: 1. I can't set the :ID value other than as it is already a parameter 2. With the IF statement included the query always fails with Syntax Errornear IF Currently, I'm going to carry on working by building different sql statement to insert or update based on wether my application knows that an INSERT or UPDATE is needed, but I doubt this is the correct way of doing it. One thing I really don't want to do is have to create stored procedures to handle all inserts / updates etc as this seems to me to bealot of extra overheadand won't actually improve the security of my application. Any help would be very gratefully received. Thanks. Simon.
