UPDATE Not working as expected

Question

Hi All
I am trying to update a column , please see the below sql codes
the first one gives correct results, but when i do an UPDATE it turns all the values to FALSE, where am i missing?

select disTINCT A.CALENDAR_DATE,A.WEEKDAY_NAME,B.CORP_HOLIDAY_IND,B.DAY_IN_FISCAL_WEEK_DESC
,a.workday_flag
,case when (a.weekday_name in ('SATURDAY','SUNDAY') OR B.CORP_HOLIDAY_IND='Y' )THEN 'FALSE' ELSE 'TRUE' END AS WORKDAY_FLAG
from dim_date_new a
inner join "PROD"."CALENDAR_DEV"."CALENDAR_DATE_NEW" B
ON A.CALENDAR_DATE=B.CALENDAR_DATE
----  WHERE B.CORP_HOLIDAY_IND='Y' OR TRIM(WEEKDAY_NAME) IN ('SATURDAY','SUNDAY') 
    order by 1

UPDATE DIM_DATE_NEW
  SET WORKDAY_FLAG='FALSE'
  WHERE CALENDAR_DATE IN 
  (
select disTINCT A.CALENDAR_DATE----,A.WEEKDAY_NAME,B.CORP_HOLIDAY_IND,B.DAY_IN_FISCAL_WEEK_DESC
from dim_date_new a
inner join "PROD"."CALENDAR_DEV"."CALENDAR_DATE_NEW" B
ON A.CALENDAR_DATE=B.CALENDAR_DATE
  WHERE (TRIM(a.weekday_name) in ('SATURDAY','SUNDAY') OR B.CORP_HOLIDAY_IND='Y' )
    ----ORDER BY 1 ,2
)

please advice.
Thanks

Answer 1

Hi @blackhills ,

For this type of problem we recommend that you post CREATE TABLE statements for your tables together with INSERT statements with sample data, enough to illustrate all angles of the problem. We also need to see the expected result of the sample.

You could also try with below update statement and check whether it is working.

 update a  
 SET a.workday_flag=  
 case when (a.weekday_name in ('SATURDAY','SUNDAY') OR B.CORP_HOLIDAY_IND='Y') THEN 'FALSE' ELSE 'TRUE' END   
 from dim_date_new a  
 inner join "PROD"."CALENDAR_DEV"."CALENDAR_DATE_NEW" B  
 ON A.CALENDAR_DATE=B.CALENDAR_DATE  

Best regards
Melissa

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

It is much easier to debug update statements if you use this structure. What does this return?

/*
 UPDATE d
   SET WORKDAY_FLAG='FALSE'
*/
SELECT d.*
   FROM DIM_DATE_NEW d
   WHERE CALENDAR_DATE IN 
   (
 select disTINCT A.CALENDAR_DATE----,A.WEEKDAY_NAME,B.CORP_HOLIDAY_IND,B.DAY_IN_FISCAL_WEEK_DESC
 from dim_date_new a
 inner join "PROD"."CALENDAR_DEV"."CALENDAR_DATE_NEW" B
 ON A.CALENDAR_DATE=B.CALENDAR_DATE
   WHERE (TRIM(a.weekday_name) in ('SATURDAY','SUNDAY') OR B.CORP_HOLIDAY_IND='Y' )
     ----ORDER BY 1 ,2
 )