Обсуждение: 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)
Kaloyan Iliev <news1@faith.digsys.bg> 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
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
Thanks a lot!<br /> It works!<br /><br /> Regards,<br /><br /> Kaloyan Iliev<br /><br /> Tom Lane wrote: <blockquote
cite="mid5031.1157638539@sss.pgh.pa.us"type="cite"><pre wrap="">Kaloyan Iliev <a class="moz-txt-link-rfc2396E"
href="mailto:news1@faith.digsys.bg"><news1@faith.digsys.bg></a>writes: </pre><blockquote type="cite"><pre
wrap="">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 </pre></blockquote><pre wrap="">
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?
<a class="moz-txt-link-freetext"
href="http://archives.postgresql.org">http://archives.postgresql.org</a>
</pre></blockquote><br />