how to update a tag column based on multiple values against an ID column-CodePudding

WOuld like to check on how I can query the following scenario?

Customer Table

In this scenario, customer CU_001 has multiple products and statuses. I need to tag CU_001 as active if one of the products has "Paying" Status or "Paying-Installment". However, if the customer has other status except the two, it will be tag as Inactive. Like CU_002, its has only one product and should be tagged as inactive.

CodePudding user response：

so you could use a query like below to give you all customers which need to be tagged

Select CustomerID
from customertable
where status in ('paying', 'paying-installment')

if the tag column is in the same table then

update ct
set tag=case when c.customerid is not null then 'active' else 'inactive' end
from
customertable ct
left join 
(
Select CustomerID
    from customertable
    where status in ('paying', 'paying-installment')
) c
on ct.CustomerID=c.CustomerID

if this is another table then simple replace

from
customertable ct

with

from
<othertablename> ct

CodePudding user response：

If this is just something you want to view, adhoc, then the below will work, as it does not set anything in the tables:

This code will add a column with the status for every line:

SELECT 
    [Customer ID],
    Product,
    Status,
    CASE
        WHEN
            Status = 'Paying'
                THEN
                    'Active'
        WHEN
            Status = 'Paying-Installment'
                THEN
                    'Active'
        ELSE
            'Inactive'
        END AS Active
FROM customerTable

This code will add a column with the status but only show the customer and the status:

SELECT DISTINCT
    [Customer ID],
    CASE
        WHEN
            Status = 'Paying'
                THEN
                    'Active'
        WHEN
            Status = 'Paying-Installment'
                THEN
                    'Active'
        ELSE
            'Inactive'
        END AS Active
FROM customerTable

The difference between the two is what columns are shown, and the DISTINCT command.