I have a query with a subquery that selects Id numbers from a table based on several conditions.
Those Id Numbers are then used in an Update statement to populate a field with a value.
The problem I have is that the conditions I have created are being ignored.
Firstly I create a table to show what type of services they have on their account - there can be 1 or more services on 1 account.
So for example, Account Number 123456 could have a homephone service, a mobile (cell phone) & an Internet service.
The table 'Account_Services' is therefore created as ;
Create Table Account_Services as
( Select Account_Number, Service_Type From Table 'A'
Group by Account_Number )
Primary Index(Account_Number, Service_Type);
Next, I write an Update query using this table;
UPDATE TABLE A
SET FIELD1 AS 'HMI'
WHERE ACCOUNT_NUMBER IN
FROM Account Services
Where Service_Type IN ( 'Homephone', 'Mobile', 'Internet' ) )
The Update happens but I find that while the Accounts with these 3 Service Types are updated, other Accounts are also updated that have other service types?
I thought I'd try to stop this by adding another condition as per below;
-- added to subquery after first condition
And Service_Type NOT IN ( 'Service_Type1' , 'Service_Type2' , 'Service_Type3' )
This still doesn't stop the incorrect updates happening??
Can anyone suggest what's going on?
I also checked for Nulls in Service_Type & there aren't any - I added the clause 'AND Service_Type IS NOT NULL'
But from here I'm stuck ...
It didnt ignore anything.
You are saying here update all the rows for a given account number if that account number has any one of the service as homephone or mobile or internet, irrespective of the service_type in target table.
If you want to update only the the service_type in target table include that also in the where clause like below
and Service_Type in (
That makes sense but Table 'A' doesn't have a 'Service_Type' field.
Are your brackets correct? Do you mean to have the second
Service_Type in ( 'Homephone', 'Mobile', 'Internet'
as a filter from table 'A'?
Table 'A' does however have the Fields Mobile, Homephone, & Internet, these contain the counts for those Service_Types.
Any further advice? I think you're very close to solving this.
I've now changed the target table to a table that has a Service_Type on each row & added the Updatable field 'FIELD1' field to accept the new Update value.
As there can be > 1 Service Type on an Account, I want the Update statement to add (for this example) 'HMI' to the FIELD1 field where the Account Number has Service_Types of Homephone AND a Mobile AND an Internet service but NO other types of Service.
I tried your script barani_sachin as per below but it is missing some Service_Types & adding others so I must be doing something wrong.
SET FIELD1 = 'HMI'
WHERE Accnt_Num IN
WHERE Service_Type IN ( 'Homephone', 'Mobile', Internet' ))
AND Service_Type IN ( 'Homephone', 'Mobile', Internet' )
Any suggests from here??
The semantics of your question are not clear to me. Do you want accounts set to 'HMI' IFF the account has each of those attributes and no other attributes?
the subquery you have does not answer that question. It gets a list of accounts that have any of H, M or I and does not exclude accounts which have other attributes in addition to one or more of H, M or I.
To get exactly H, M and I and no others, more logic is required. Eg in your CREATE TABLE AS (not tested):
CT Account_Services AS (SELECT account_number, MAX(CASE WHEN service_type='homephone' THEN 1 ELSE 0) AS H, MAX(CASE WHEN service_type='internet' THEN 1 ELSE 0) AS I,MAX(CASE WHEN service_type='mobile' THEN 1 ELSE 0) AS M GROUP BY account_number) Unique Primary Index (account_number);
Then your update would be:
UPDATE A SET Field1='HMI' WHERE account_number IN (Select account_number FROM Account_services WHERE H=1 AND I=1 and M=1);
Of course you don't really need the create table, you can substitute the query from the CT instead of the subquery in the update and add a HAVING clause with the "WHERE H=1 AND I=1 and M=1" condition.
I guess you will have to define the columns in the create table statement with the datatypes.
The MAX is required to allow all the flags to be grouped onto one row for the account.
Without the GROUP/MAX you get one row per account_number/service_type, so this updates all accounts where any of those service_types exists.
There's another solution to get an account where all service_types exist:
SET FIELD1 = 'HMI'
WHERE ACCOUNT_NUMBER IN
WHERE Service_Type IN ( 'Homephone', 'Mobile', 'Internet' )
GROUP BY 1
HAVING COUNT(*) = 3
This assumes that the combination account_number/service_type is unique, otherwise simply replace COUNT(*) with COUNT(DISTINCT Service_Type).
I'm still struggling with the datatype error when I try to do the update part that ToddAWalter has kindly provided.
I think the datatype created (BYTEINT) in the below line is causing the error so I want to force it to make the field an INTEGER datatype;
MAX(CASE WHEN service_type='homephone' THEN 1 ELSE 0) AS H
Is there a way to define the datatype to INTEGER during the Create table process that ToddAWalter describes?