Win a copy of The Java Performance Companion this week in the Performance forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Regarding the SQL querry generation

 
Naveen Koneti
Greenhorn
Posts: 13
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi In my project I have to construct queries and write them into sql files through java and they will be read by another program which will be executing the script line by line,but the problem has come while generating the sql file...

Now I am generating the sql file with insert statements like this

"insert into service_template_device (service_template_id, dev_type_id) select "+ serviceTemplateId + ", "+ devTypeId+ " from dual where not exists select 1 from service_template_device where service_template_id="+serviceTemplateId+ " and dev_type_id="+ devTypeId + ");" + "\r\n"

this when executed will check whether the record exists if not then it will insert other wise ignore but my requirement is I have to update the Database if the record doesnot exist.

I tried with merge statement but the problem is it is not a sequential update there are constraints in the tables.As number of sql's is huge the implementation is quite complex.Is there a simple way to do this ?

Please suggest me solution for the above problem....
 
Paul Campbell
Ranch Hand
Posts: 338
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Originally posted by Naveen Koneti:
Hi In my project I have to construct queries and write them into sql files through java and they will be read by another program which will be executing the script line by line,but the problem has come while generating the sql file...

Now I am generating the sql file with insert statements like this

"insert into service_template_device (service_template_id, dev_type_id) select "+ serviceTemplateId + ", "+ devTypeId+ " from dual where not exists


this when executed will check whether the record exists if not then it will insert other wise ignore but my requirement is I have to update the Database if the record doesnot exist.

I tried with merge statement but the problem is it is not a sequential update there are constraints in the tables.As number of sql's is huge the implementation is quite complex.Is there a simple way to do this ?

Please suggest me solution for the above problem....


Hi Naveen,

What you are attempting to do is beyond a DML statement (select, insert, delete, update). You have to either do the programming in your application or in a stored procedure to determine if the data exists. Using the correlated subquery is only allowed if you are populating your data with a result set from your select.

In SQL stored procedure I would do the select for my records:

SELECT 1
FROM service_template_device
WHERE service_template_id = 'serviceTemplateId'
AND dev_type_id = 'devTypeId';

-- return something here to let my application know it already exists

Provide an exception block to trap no_data_found and insert the data within the no_data_found exception block.

It is probably more efficient to have the database do it, but it would make your application less portable between RDBMS.
 
Paul Campbell
Ranch Hand
Posts: 338
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Originally posted by Naveen Koneti:

"insert into service_template_device (service_template_id, dev_type_id) select "+ serviceTemplateId + ", "+ devTypeId+ " from dual where not exists select 1 from service_template_device where service_template_id="+serviceTemplateId+ " and dev_type_id="+ devTypeId + ");" + "\r\n"



Naveen, I understand what you are trying to do here.

insert into service_template_device (service_template_id, dev_type_id)
select serviceTemplateId, devTypeId
from dual
where not exists ( select 1
from service_template_device
where service_template_id = serviceTemplateId
and dev_type_id = devTypeId );

Should work.
 
Raees Uzhunnan
Ranch Hand
Posts: 126
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
What I understood is if record does not exist insert otherwise update !!

Merge is used for same purpose however complex it may be; and I don't understand what you are talking about constraints and stuff to do with merge !!

Another way to do it is call a proc ; pass all the parameters ; inside the proc inset if it fails update !!!
 
Paul Campbell
Ranch Hand
Posts: 338
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Raees,

I thought the same thing as well... but after looking at the SQL the poster seems to be trying to insert if the data does not exist. There isn't any SQL to support an update.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic