Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 3534
  • Last Modified:

Delete from one table while joining to another using informix dynamic server

I think I've used this delete syntax with other databases, and it's worked ok, but it doesn't seem to in informix. I know how to get it working using a temp table, but I'm hoping there's a more elegant way. Can anyone suggest how to make this work without using a temp table?

I'm wanting to delete from the _mc_cost table, but not the mc_batch_header table. I get the error "201: A syntax error has occurred."
Have tried this:
 
delete _mc_cost.*
from _mc_cost inner join mc_batch_header on _mc_cost.batch_id = mc_batch_header.batch_id
where (external_id = '2846'
       and cost_amount = 20473.92
       and mc_batch_header.batch_descr = 'blah')
   or (external_id = '2847'
       and cost_amount = 42048
       and mc_batch_header.batch_descr = 'blah')
   or (external_id = '2624'
       and cost_amount = 76069.07
       and mc_batch_header.batch_descr = 'blah2')
   or (external_id = '2625'
       and cost_amount = 88168
       and mc_batch_header.batch_descr = 'blah2');
 
and this:
 
delete
from _mc_cost inner join mc_batch_header on _mc_cost.batch_id = mc_batch_header.batch_id
where (external_id = '2846'
       and cost_amount = 20473.92
       and mc_batch_header.batch_descr = 'blah')
   or (external_id = '2847'
       and cost_amount = 42048
       and mc_batch_header.batch_descr = 'blah')
   or (external_id = '2624'
       and cost_amount = 76069.07
       and mc_batch_header.batch_descr = 'blah2')
   or (external_id = '2625'
       and cost_amount = 88168
       and mc_batch_header.batch_descr = 'blah2');

Open in new window

0
Terry Woods
Asked:
Terry Woods
  • 2
2 Solutions
 
Kevin CrossChief Technology OfficerCommented:
Hello TerryAtOpus,

I am not the greatest at Informix, but for SQL Syntax (T-SQL) I would normally do something like this:
DELETE c
FROM _mc_cost c INNER JOIN mc_batch_header h
ON c.batch_id = h.batch_id
WHERE ...

For Informix, I also saw this syntax on doing delete with using statement for join (see code snippet and link below).

http://publib.boulder.ibm.com/infocenter/idshelp/v10/index.jsp?topic=/com.ibm.sqlt.doc/sqltmst130.htm

Hopefully that helps.

Best regards,

mwvisa1
delete from _mc_cost
using _mc_cost, mc_batch_header 
where _mc_cost.batch_id = mc_batch_header.batch_id
and (( _mc_cost.external_id = '2846'
       and  _mc_cost.cost_amount = 20473.92
       and mc_batch_header.batch_descr = 'blah')
   or ( _mc_cost.external_id = '2847'
       and  _mc_cost.cost_amount = 42048
       and  _mc_cost.mc_batch_header.batch_descr = 'blah')
   or ( _mc_cost.external_id = '2624'
       and  _mc_cost.cost_amount = 76069.07
       and mc_batch_header.batch_descr = 'blah2')
   or ( _mc_cost.external_id = '2625'
       and  _mc_cost.cost_amount = 88168
       and mc_batch_header.batch_descr = 'blah2'));

Open in new window

0
 
Terry WoodsIT GuruAuthor Commented:
The "using" syntax you provided looks like it is the way Informix allows it, but only if you have XPS (Extended Parallel Server) installed (which I don't), whatever that is.

I guess I'll just stick with the temp table technique! :-)

Thanks for your help - the link was useful in at least telling me it can't be done so easily.

Both these methods work I think, one (though not pretty) not needing a temp table:
delete from _mc_cost
where (external_id = '2846'
       and cost_amount = 20473.92
       and (select batch_descr from mc_batch_header where batch_id = _mc_cost.batch_id) = 'blah')
   or (external_id = '2847'
       and cost_amount = 42048
       and (select batch_descr from mc_batch_header where batch_id = _mc_cost.batch_id) = 'blah')
   or (external_id = '2624'
       and cost_amount = 76069.07
       and (select batch_descr from mc_batch_header where batch_id = _mc_cost.batch_id) = 'blah2')
   or (external_id = '2625'
       and cost_amount = 88168
       and (select batch_descr from mc_batch_header where batch_id = _mc_cost.batch_id) = 'blah2')
 
or alternatively the old temp table way:
 
select transaction_id  --the primary key
from _mc_cost inner join mc_batch_header on _mc_cost.batch_id = mc_batch_header.batch_id
where (external_id = '2846' 
       and cost_amount = 20473.92
       and mc_batch_header.batch_descr = 'blah')
   or (external_id = '2847'
       and cost_amount = 42048
       and mc_batch_header.batch_descr = 'blah')
   or (external_id = '2624' 
       and cost_amount = 76069.07
       and mc_batch_header.batch_descr = 'blah2')
   or (external_id = '2625' 
       and cost_amount = 88168
       and mc_batch_header.batch_descr = 'blah2')
into temp tmp_tasks_to_delete with no log;
 
delete from _mc_cost
where transaction_id in (select transaction_id from tmp_tasks_to_delete);
 
drop table tmp_tasks_to_delete;

Open in new window

0
 
Kevin CrossChief Technology OfficerCommented:
Glad to hear it Terry and equally so that you have workaround.

I was hoping that one of the two samples would help inspire you to the solution...

Happy coding!

Kev
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now