chicho12
asked on
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')
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')
SOLUTION
![Avatar of Guy Hengel [angelIII / a3]](https://filedb.experts-exchange.com/files/public/2013/11/25/ddbe0ea6-aa6e-4279-b781-57b735dd3ea0.jpeg){kind=small}
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
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
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')
========
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')
ASKER
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.
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.
>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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.