[SQL] sql-porting-problem oracle to postgresql with UPDATE/IS NOT NULL

[Gau, Hans-Jürgen]

hello list, 
i have some problems with an sql-statement which runs on oracle but not on
postgresql (i want update only if result of SELECT is not empty, the
SELECT-queries are identical):

UPDATE table1 t1 
SET (t1.id) = 
(SELECT h.id FROM table2 t2,table3 t3, table1 t1 
WHERE t3.field = t2.field 
AND t2.id = t1.id 
AND t1.id <> t3.id) 
WHERE 
(SELECT h.id FROM table2 t2,table3 t3, table1 t1 
WHERE t3.field = t2.field 
AND t2.id = t1.id 
AND t1.id <> t3.id) IS NOT NULL; 



thanks, hans 

Re: [SQL] sql-porting-problem oracle to postgresql with UPDATE/IS NOT NULL

[Daryl Richter]

On Jul 28, 2009, at 5:58 AM, Gau, Hans-Jürgen wrote:


hello list,
i have some problems with an sql-statement which runs on oracle but  
not on postgresql (i want update only if result of SELECT is not  
empty, the SELECT-queries are identical):


UPDATE table1 t1
SET (t1.id) =
(SELECT h.id FROM table2 t2,table3 t3, table1 t1
WHERE t3.field = t2.field
AND t2.id = t1.id
AND t1.id <> t3.id)
WHERE
(SELECT h.id FROM table2 t2,table3 t3, table1 t1
WHERE t3.field = t2.field
AND t2.id = t1.id
AND t1.id <> t3.id) IS NOT NULL;


Try this:

UPDATE table1 t1
SET (t1.id) =
(SELECT h.id FROM table2 t2,table3 t3, table1 t1
WHERE t3.field = t2.field
AND t2.id = t1.id
AND t1.id <> t3.id)
WHERE
EXISTS (SELECT 1 FROM table2 t2,table3 t3, table1 t1
WHERE t3.field = t2.field
AND t2.id = t1.id
AND t1.id <> t3.id

AND h.id IS NOT NULL);






thanks, hans




--
Daryl
http://itsallsemantics.com

""Everyone thinks of changing the world, but no one thinks of changing  
himself."

- Leo Tolstoy


--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

[SQL] Detect referential integrity structure

[Akos Gabriel]

Hi,

I've a big/complex database (Adempiere - www.adempiere.org ) where I'd
like to delete some rows from some tables (delete a client and its
data from the ERP database).
All tables are in one schema (adempiere).
There are some foreign keys/constraints.
I've following options:

- disable all constraints, doing the delete, enable all constraints.
- find out the "right sequence" and do the delete -s.

Does anyone have such an algorythm, or a method of disabling all the
constraints? We have 8.3.7 right now. The solution will be published
into the Adempiere wiki / contributed to the project as well.

Thanks in advance,
Akos Gabriel

-- 
Üdvözlettel,
Gábriel Ákos
-=E-Mail :akos.gabr...@i-logic.hu|Web:  http://www.i-logic.hu=-
-=Tel/fax:+3612391618|Mobil:+36209278894 =-

-- 
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

Re: [SQL] Detect referential integrity structure

[Rob Sargent]

Perhaps another option:

Alter the references to ON DELETE CASCADE as seen here 




Akos Gabriel wrote:

Hi,

I've a big/complex database (Adempiere - www.adempiere.org ) where I'd
like to delete some rows from some tables (delete a client and its
data from the ERP database).
All tables are in one schema (adempiere).
There are some foreign keys/constraints.
I've following options:

- disable all constraints, doing the delete, enable all constraints.
- find out the "right sequence" and do the delete -s.

Does anyone have such an algorythm, or a method of disabling all the
constraints? We have 8.3.7 right now. The solution will be published
into the Adempiere wiki / contributed to the project as well.

Thanks in advance,
Akos Gabriel

  


--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

Re: [SQL] sql-porting-problem oracle to postgresql with UPDATE/IS NOT NULL

[nha]

Hello,

Le 28/07/09 14:25, Daryl Richter a écrit :
> 
> On Jul 28, 2009, at 5:58 AM, Gau, Hans-Jürgen wrote:
> 
>> hello list,
>> i have some problems with an sql-statement which runs on oracle but
>> not on postgresql (i want update only if result of SELECT is not
>> empty, the SELECT-queries are identical):
>>
>> UPDATE table1 t1
>> SET (t1.id) =
>> (SELECT h.id FROM table2 t2,table3 t3, table1 t1
>> WHERE t3.field = t2.field
>> AND t2.id = t1.id
>> AND t1.id <> t3.id)
>> WHERE
>> (SELECT h.id FROM table2 t2,table3 t3, table1 t1
>> WHERE t3.field = t2.field
>> AND t2.id = t1.id
>> AND t1.id <> t3.id) IS NOT NULL;
>>
> Try this:
> 
> UPDATE table1 t1 [...]
> WHERE
> EXISTS (SELECT 1 FROM table2 t2,table3 t3, table1 t1
> WHERE t3.field = t2.field
> AND t2.id = t1.id
> AND t1.id <> t3.id
> 
> AND h.id IS NOT NULL);
> 

Beyond the solution brought by Daryl Richter, it seems that "h" is an
unbound alias in the original (and also in the suggested) query. Some
clarification would be helpful for further investigation.

Regards.

--
nha / Lyon / France.

-- 
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

[SQL] Tweak sql result set... ?

[Axe]

I have a problem where I want to tweak a simple select in an
"unobtrusive way". Imagine I have the following select statement:
"SELECT name FROM customer LIMIT 1" and I get a normal result set from
this. But, could I,maybe by defining some other function or similar,
change the result set *without* changing the query? Suppose I get the
result from the query above, saying: "Peter Peterson". I would
(sometimes) like to get the result "Peter Peterson" but I
should not have to change the original query.

I know I could write "SELECT '' || name || '' as name FROM
customer" but then I have altered the original query and I cannot do
this since it is supposed to function different in two different
situations.

Any ideas on how to achieve this? I would like to let the original sql
code stay original. I can prepare postgres before executing the sql if
this makes it easier to acheive the goal

-- 
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

Re: [SQL] Tweak sql result set... ?

[Tim Landscheidt]

(anonymous) wrote:

> I have a problem where I want to tweak a simple select in an
> "unobtrusive way". Imagine I have the following select statement:
> "SELECT name FROM customer LIMIT 1" and I get a normal result set from
> this. But, could I,maybe by defining some other function or similar,
> change the result set *without* changing the query? Suppose I get the
> result from the query above, saying: "Peter Peterson". I would
> (sometimes) like to get the result "Peter Peterson" but I
> should not have to change the original query.

> I know I could write "SELECT '' || name || '' as name FROM
> customer" but then I have altered the original query and I cannot do
> this since it is supposed to function different in two different
> situations.

> Any ideas on how to achieve this? I would like to let the original sql
> code stay original. I can prepare postgres before executing the sql if
> this makes it easier to acheive the goal

Have a look at CREATE RULE.

Tim


-- 
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql