EXISTS or IN both not working 1

[gwoman]

Below is a query I have been working on and I get an EXISTS error when I run it using IN ... and I get a syntax error when I use EXISTS ....

UPDATE WELL_DATA_TBL_TMP INNER JOIN DWRPTG_CMPL_DMN ON WELL_DATA_TBL_TMP.API_Number=DWRPTG_CMPL_DMN.WELL_API_NBR SET WELL_DATA_TBL_TMP.STATUS = DWRPTG_CMPL_DMN.CMPL_STATE_TYPE_DESC
WHERE DWRPTG_CMPL_DMN.CMPL_STATE_TYPE_DESC<>WELL_DATA_TBL_TMP.STATUS
IN (SELECT DWRPTG_CMPL_DMN.WELL_API_NBR, Count(DWRPTG_CMPL_DMN.CMPL_FAC_ID) AS CountOfCMPL_FAC_ID, DWRPTG_CMPL_DMN.CMPL_STATE_TYPE_DESC
FROM DWRPTG_CMPL_DMN
WHERE (((DWRPTG_CMPL_DMN.CMPL_STATE_TYPE_DESC)="Permanently Abandoned"))
GROUP BY DWRPTG_CMPL_DMN.WELL_API_NBR, DWRPTG_CMPL_DMN.CMPL_STATE_TYPE_DESC
HAVING (((Count(DWRPTG_CMPL_DMN.CMPL_FAC_ID))>1)));

Any help would be greatly appreciated ...

Regards ...
gwoman

 

[Golom]

The syntax is

WHERE SomeField IN (... a list of values ...)

You have

WHERE SomeField [red]<> SomeOtherField[/red] IN (... a list of values ...)

 

[gwoman]

Thanks ...
I am getting the same errors.
If I run the queries separately they run with no problems ... do you see anything else that could be causing the problem?

Thanks again ...

Regards ...
gwoman

 

[Golom]

Another problem is the sub-query for the IN clause

SELECT DWRPTG_CMPL_DMN.WELL_API_NBR, 
       Count(DWRPTG_CMPL_DMN.CMPL_FAC_ID) AS CountOfCMPL_FAC_ID, 
       DWRPTG_CMPL_DMN.CMPL_STATE_TYPE_DESC

This returns three fields but an IN-clause requires a single list of values (i.e. only one field in the sub-select).

 

[gwoman]

Aha ... thanks!

have a good one!

gwoman

 

[gwoman]

Is it possible to make a tmp table about of the subquery and use that in the IN clause?
like so ...

UPDATE WELL_DATA_TBL_TMP INNER JOIN DWRPTG_CMPL_DMN ON WELL_DATA_TBL_TMP.API_Number=DWRPTG_CMPL_DMN.WELL_API_NBR SET WELL_DATA_TBL_TMP.STATUS = DWRPTG_CMPL_DMN.CMPL_STATE_TYPE_DESC
WHERE DWRPTG_CMPL_DMN.CMPL_STATE_TYPE_DESC<>WELL_DATA_TBL_TMP.STATUS
IN([WELL_DATA_PA]);

thanks ...

gwoman

 

[Golom]

Assuming that WELL_DATA_PA is a table with one field, you would need

IN (Select TheField From [WELL_DATA_PA]);

 

[gwoman]

Thanks ... it runs ... but the update isn't happening.
I'm going back through my queries now to find where the break down is ... thanks again for your help!

regards ...
gwoman