Forum Discussion
Update a column in SQL based on field frequency
Hello SQL query team ,
I need to create a dimension in my database ( to create later a report in Power BI) and I am struggling with the stored procedure. In fact, I need to have the following table dim_table as a result.
| passport number | passport_expiry_date | is_valid_expiry_date | consolidated_expiry_date |
| ALJDLJZ | 7/10/2020 | 1 | 7/10/2020 |
| ALJDLJZ | 7/10/2020 | 1 | 7/10/2020 |
| ALJDLJZ | NULL | 1 | 7/10/2020 |
| ALJDLJZ | 8/10/2021 | 1 | 7/10/2020 |
DONE: I have already the passport numbers and passport expiry dates in the table and and I have updated the columns 'is_valid_expiry_date' to consider NULL and dates in the future as valid..
TO BE DONE: I need to update the consolidated column based on these rules:
- we consider just records with is_valid_expiry_date=1
-if count( distinct(passport_expiry_date))=1 (just one valid date)then, this latter should be in each line in the consolitaed column.
- if we have different dates ( as in our example), we need to take the most frequent one (7/10/2020 (repeated twice)) and put it in each line as a consolidated value.
--------------------------
update t
[consolidated_passport_expiry_date]=
CASE WHEN count(distinct [passport_exipiry_date])=1 OR count(distinct [passport_exipiry_date])=0 THEN t.passport_exipiry_date
WHEN.........................
WHEN..................
ELSE
END
FROM dim_table t
where t.passport_expiry_dateis_valid_expiry_date=1
GROUP BY t.[passport_number], [passport_exipiry_date]
------------------------
Thanks in advance for any help or tips to complete this update section.
Sabrina
That's not a valid SQL syntax
UPDATE table
SET field= CASE WHEN THEN ELSE END
WHERE <filter condition>
The FROM statement is only needed when you update one table's columns from another related table's content.
4 Replies
That's not a valid SQL syntax
UPDATE table
SET field= CASE WHEN THEN ELSE END
WHERE <filter condition>
The FROM statement is only needed when you update one table's columns from another related table's content.
Hi sabrinekalbousi ,
If you think lbendlin's answer is helpful, please accept it as a solution. And if you still need help, it is better to get dedicated support from the database forum.
I'm guessing you added "_Perfect" to indicate you thought the past perfect should be used.
The query first calculates the category frequencies and stores them in a temporary table. Then, it uses this temporary table to update the products table. Finally, to ensure a clean environment, the temporary table is dropped. This temporary table approach can sometimes be faster than the subquery method, particularly for very large tables.
I'm guessing you added "_Perfect" to indicate you thought the past perfect should be used.
The query first calculates the category frequencies and stores them in a temporary table. Then, it uses this temporary table to update the products table. Finally, to ensure a clean environment, the temporary table is dropped. This temporary table approach can sometimes be faster than the subquery method, particularly for very large tables.
