CODEWITHSUNDEEP
sqldatabasepostgresqlperformance
inlineMacro
inlineMacro

Reputation: 67

need help to form a optimised update sql query

I have a select query with where clause and want to write an update for the same condition.

But I am unable to form one.

So I get the output resultSet and traverse the resultSet to update the table [see below]

The select query:

select 
    a.businesskey, a.featurekey, c.businesskey, c.featurekey 
from 
    device a, 
    (select concat(‘customPrefix’,ipaddr,’]’) clmna, instance_id 
     from wirelessdevice) as b, 
    device c 
where 
    a.businesskey = b.clmna 
    and c.uniqueid = b.instance_id;

Present method to update:

string strQuery = "select a.businesskey, a.featurekey, c.businesskey, c.featurekey from device a, (select concat(‘customPrefix’,ipaddr,’]’) clmna, instance_id from wirelessdevice) as b, device c where a.businesskey=b.clmna and c.uniqueid=b.instance_id;";
ResultSet resultSet = statement.executeQuery();
while(resultSet.hasnext()){
        String srcbkey = resultSet.getString(1);
        String srcFtrkey = resultSet.getString(2);
        String destBkey = resultSet.getString(3);
        String destFtrkey = resultSet.getString(4);
        String updateQuery = "update device set featurekey ='" + destFtrkey +"' where businesskey ='" + srcFtrkey +"';";
        statement.executeQuery(updateQuery);
}

is it possible to write a single update query instead of recursively going through the resultset ?

Upvotes: 0

Views: 53

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270713

Yes. I find your code hard to follow, but it is something like this:

update device d
    set d.featurekey = concat('customPrefix', wd.clmna, wd.ipaddr, ']')
    from wirelessdevice wd
    where d.businesskey= wd.clmna and
          d.uniqueid= wd.instance_id;

Upvotes: 1

Related Questions