delete query

I am having problem with the query below.
I need to delete rows in table LLC.GAPW12_COMPPRIC_TB where GAPW01.CU_ID_NR = '10502187'  and AND GAPW12.FEE_TYPE_ID_NR ='0000000000000000009'
But what is happening is that that its deleting all rows in LLC.GAPW12_COMPPRIC_TB  where the GAPW011.PRIC_MODEL_ID_NR  is the same as the same as the records where GAPW01.CU_ID_NR = '10502187'  and AND GAPW12.FEE_TYPE_ID_NR ='0000000000000000009'
So its deleting more records than I want to.
Any sugestions on what I am doing wrong?
Thank you.

DELETE FROM LLC.GAPW12_COMPPRIC_TB
WHERE PRIC_MODEL_ID_NR IN
(select GAPW011.PRIC_MODEL_ID_NR
from LLC.GAPW01_GAPCNFG_TB GAPW01, LLC.GAPW11_PRICMODL_TB  GAPW011, LLC.GAPW18_FEETYPE_TB  GAPW18, LLC.GAPW12_COMPPRIC_TB GAPW12
where  GAPW01.GAP_CNFG_ID_NR = GAPW011.GAP_CNFG_ID_NR
AND GAPW011.PRIC_MODEL_ID_NR = GAPW12.PRIC_MODEL_ID_NR
AND GAPW01.CU_ID_NR = '10502187'
AND GAPW12.FEE_TYPE_ID_NR ='0000000000000000009')
chicho12Asked:
Who is Participating?
 
imran_fastCommented:
hi,
Is this query returning any record

select GAPW011.PRIC_MODEL_ID_NR  from
LLC.GAPW01_GAPCNFG_TB GAPW01
inner join
LLC.GAPW11_PRICMODL_TB  GAPW011 on
GAPW01.GAP_CNFG_ID_NR = GAPW011.GAP_CNFG_ID_NR
inner join
LLC.GAPW12_COMPPRIC_TB GAPW12 on
GAPW011.PRIC_MODEL_ID_NR = GAPW12.PRIC_MODEL_ID_NR
inner join
LLC.GAPW18_FEETYPE_TB  GAPW18 on
GAPW012.FEE_TYPE_ID = GAPW18.FEE_TYPE_ID

where  
GAPW01.CU_ID_NR = '10502187'
AND GAPW12.FEE_TYPE_ID_NR ='0000000000000000009'


try changing the last inner join with an outer join and see if you are getting the desired result.
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
can you post more about the data samples, and those rows that get deleted?

DELETE LLC.GAPW12_COMPPRIC_TB
FROM LLC.GAPW12_COMPPRIC_TB GAPW12
JOIN LLC.GAPW01_GAPCNFG_TB GAPW01
  ON GAPW01.GAP_CNFG_ID_NR = GAPW011.GAP_CNFG_ID_NR
 AND GAPW01.CU_ID_NR = '10502187'
JOIN LLC.GAPW11_PRICMODL_TB  GAPW011
  ON GAPW011.PRIC_MODEL_ID_NR = GAPW12.PRIC_MODEL_ID_NR
JOIN LLC.GAPW18_FEETYPE_TB  GAPW18
  ON <this condition is missing>
WHERE GAPW12.FEE_TYPE_ID_NR ='0000000000000000009'
0
 
chicho12Author Commented:
angelIII,
I think the condition tha is missing would be:
ON GAPW012.FEE_TYPE_ID = GAPW18.FEE_TYPE_ID

What would you like to kow about the rows that get deleted?
Thanks.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
so, can you try out this delete query:

DELETE LLC.GAPW12_COMPPRIC_TB
FROM LLC.GAPW12_COMPPRIC_TB GAPW12
JOIN LLC.GAPW01_GAPCNFG_TB GAPW01
  ON GAPW01.GAP_CNFG_ID_NR = GAPW011.GAP_CNFG_ID_NR
 AND GAPW01.CU_ID_NR = '10502187'
JOIN LLC.GAPW11_PRICMODL_TB  GAPW011
  ON GAPW011.PRIC_MODEL_ID_NR = GAPW12.PRIC_MODEL_ID_NR
JOIN LLC.GAPW18_FEETYPE_TB  GAPW18
  ON GAPW012.FEE_TYPE_ID = GAPW18.FEE_TYPE_ID
WHERE GAPW12.FEE_TYPE_ID_NR ='0000000000000000009'

resp: does this query return those rows to be deleted?

SELECT LLC.GAPW12_COMPPRIC_TB.*
FROM LLC.GAPW12_COMPPRIC_TB GAPW12
JOIN LLC.GAPW01_GAPCNFG_TB GAPW01
  ON GAPW01.GAP_CNFG_ID_NR = GAPW011.GAP_CNFG_ID_NR
 AND GAPW01.CU_ID_NR = '10502187'
JOIN LLC.GAPW11_PRICMODL_TB  GAPW011
  ON GAPW011.PRIC_MODEL_ID_NR = GAPW12.PRIC_MODEL_ID_NR
JOIN LLC.GAPW18_FEETYPE_TB  GAPW18
  ON GAPW012.FEE_TYPE_ID = GAPW18.FEE_TYPE_ID
WHERE GAPW12.FEE_TYPE_ID_NR ='0000000000000000009'

if the query returns too many rows, amend it so it does return what you want to delete, and "change it back" to the delete query syntax
0
 
imran_fastCommented:
try this one
========
DELETE FROM LLC.GAPW12_COMPPRIC_TB
WHERE PRIC_MODEL_ID_NR in
(
select GAPW011.PRIC_MODEL_ID_NR  from
LLC.GAPW01_GAPCNFG_TB GAPW01
inner join
LLC.GAPW11_PRICMODL_TB  GAPW011 on
GAPW01.GAP_CNFG_ID_NR = GAPW011.GAP_CNFG_ID_NR
inner join
LLC.GAPW12_COMPPRIC_TB GAPW12 on
GAPW011.PRIC_MODEL_ID_NR = GAPW12.PRIC_MODEL_ID_NR
inner join
LLC.GAPW18_FEETYPE_TB  GAPW18 on
GAPW012.FEE_TYPE_ID = GAPW18.FEE_TYPE_ID

where  
GAPW01.CU_ID_NR = '10502187'
AND GAPW12.FEE_TYPE_ID_NR ='0000000000000000009')
0
 
chicho12Author Commented:
I did made a mistake in the name of a table.
AngellIII,
I tried:

DELETE LLC.GAPW12_COMPPRIC_TB
FROM LLC.GAPW12_COMPPRIC_TB GAPW12
JOIN LLC.GAPW01_GAPCNFG_TB GAPW01
  ON GAPW01.GAP_CNFG_ID_NR = GAPW11.GAP_CNFG_ID_NR
 AND GAPW01.CU_ID_NR = '10502187'
JOIN LLC.GAPW11_PRICMODL_TB  GAPW11
  ON GAPW11.PRIC_MODEL_ID_NR = GAPW12.PRIC_MODEL_ID_NR
JOIN LLC.GAPW18_FEETYPE_TB  GAPW18
  ON GAPW12.FEE_TYPE_ID_NR = GAPW18.FEE_TYPE_ID_NR
WHERE GAPW12.FEE_TYPE_ID_NR ='0000000000000000009'

but I get the error " GAPW11.GAP_CNFG_ID_NR" could not be bound.

imran_fast ,
I tried :

DELETE FROM LLC.GAPW12_COMPPRIC_TB
WHERE PRIC_MODEL_ID_NR in
(
select GAPW11.PRIC_MODEL_ID_NR  from
LLC.GAPW01_GAPCNFG_TB GAPW01
inner join
LLC.GAPW11_PRICMODL_TB  GAPW11 on
GAPW01.GAP_CNFG_ID_NR = GAPW11.GAP_CNFG_ID_NR
inner join
LLC.GAPW12_COMPPRIC_TB GAPW12 on
GAPW11.PRIC_MODEL_ID_NR = GAPW12.PRIC_MODEL_ID_NR
inner join
LLC.GAPW18_FEETYPE_TB  GAPW18 on
GAPW12.FEE_TYPE_ID_NR  = GAPW18.FEE_TYPE_ID_NR
where  
GAPW01.CU_ID_NR = '10502187'
AND GAPW12.FEE_TYPE_ID_NR = '0000000000000000009')

BUt no records get deleted.

Thanks.



0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
>but I get the error " GAPW11.GAP_CNFG_ID_NR" could not be bound.
that means that the join condition is using a wrong column name, ie GAP_CNFG_ID_NR is NOT a column from table LLC.GAPW11_PRICMODL_TB

Again, please try to get the SELECT query to work, then change it to the delete query
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.