Update column based on where group by having count = 1-CodePudding

I'm looking to update a blank column with either a PledgeId or anything to populate the new column I've created to update.

I want to update the field. I have tried numerous different ways to update the field.

The code I am using that narrows what I am looking for, but can't seem to update the field with it due to different errors:

select IDPledge
from PledgePaymentSchedule
where DatePaid != ''
group by IDPledge
having count(IDPledge) = 1

I am looking for specific records that have a DatePaid that is not blank, grouped by IDPledge, having a count of exactly one. Meaning a one time Pledge.

The most recent variant of many different tries:

Update [PledgePaymentSchedule] 
set [OneTimePledge] = 'Yes'
where exists
(SELECT count([IDPledge]) FROM [PledgePaymentSchedule]
where [DatePaid] !=  ''
group by [IDPledge]
having count([IDPledge]) = 1)

This updates all records with 'Yes' instead of just the 26 records that pull from the where exists statement.

Update s
set [OneTimePledge] = (SELECT distinct count(*)         FROM [PledgePaymentSchedule]
group by [IDPledge]
having count([IDPledge]) > 1)
FROM [PledgePaymentSchedule] s
where [DatePaid] !=  ''

This variance throws error:

Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression. The statement has been terminated.

Ultimately I need an update statement, that updates column [OneTimePledge] with the results of the select statement at the top.

Thanks!

CodePudding user response：

If I got this correctly, you want to create a flag indicating that some IDPledge has one (and only one) valid DatePaid value.

It that's the case, I'd suggest working with Common Table Expressions (CTEs) to help you build your update statement:

WITH
UpdateJoin AS
(
    SELECT
        OneTimePledge
    FROM
        PledgePaymentSchedule
    WHERE
        IDPledge IN
        (
            select IDPledge
            from PledgePaymentSchedule
            where DatePaid != ''
            group by IDPledge
            having count(IDPledge) = 1
        )
)
UPDATE
    UpdateJoin
SET
    OneTimePledge = 'Yes';

Isolating the rows you want to update with CTEs usually helps.