CODEWITHSUNDEEP
javapostgresqlserialization
Mani Deep
Mani Deep

Reputation: 1356

query inside query to 1 query postgresql

i have a query like this

UPDATE FOLLOWUP F SET CUSTOMER=(SELECT NAME FROM CUSTOMERS C WHERE F.CUST_ID=C.ID), PHONE=(SELECT SEARCHKEY FROM CUSTOMERS C WHERE F.CUST_ID=C.ID);

this is succesfully working in my postgresql, but not running in java

and my java function

for (final TicketLineInfo l : ticket.getLines())
    if(l.getConsumption()!= 0.0 && l.getMultiply()!=l.getConsumption()) {            
        new PreparedSentence(s
                , "UPDATE FOLLOWUP F SET CUSTOMER=(SELECT NAME FROM CUSTOMERS C WHERE F.CUST_ID=?), PHONE=(SELECT SEARCHKEY FROM CUSTOMERS C WHERE F.CUST_ID=?);"
                , SerializerWriteParams.INSTANCE
                ).exec(new DataParams() { public void writeValues() throws BasicException {
                        setString(1, ticket.getCustomerId());
                        setString(2, ticket.getCustomerId());
                }});
                 }

when i run this

i get error like this:

com.openbravo.basic.BasicException: 
org.postgresql.util.PSQLException: ERROR: more than one row returned by a subquery used as an expression

org.postgresql.util.PSQLException: 
ERROR: more than one row returned by a subquery used as an expression

my question is how do i convert the above query into one single query without a subquery.. so that i can avoid this exception and proceed with my execution OR is there any other serializer which supports this

Upvotes: 1

Views: 364

Answers (1)

roman
roman

Reputation: 117485

Looks like you don't need subqueries in your query:

update FOLLOWUP as F set
    Name = c.Name,
    Phone = c.SearchKey
from CUSTOMERS as c
where
    F.Cust_ID = C.ID and
    F.Cust_ID = ?

Upvotes: 2

Related Questions