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: 210
  • Last Modified:

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')
0
chicho12
Asked:
chicho12
  • 3
  • 2
  • 2
2 Solutions
 
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
 
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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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
 
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

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

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