CODEWITHSUNDEEP
sql-server
ADY
ADY

Reputation: 113

SQL: Update Rows That Are Unique Based on a Column

I have a table that associates contacts to companies. When more then one contact is added there is a option to set a "Primary" or "Secondary" contact. However when people have been adding a contact they haven't been using this function. The program was modified so it automatically marks the first contact added to a company as primary but that doesn't fix the data already entered.

I would like to poll the table and say if there is only one record per CompanyID set the PrimaryContact to true but I cannot figure out how to do this. For any company that has more then one contact assigned I don't want to do anything. For example:

CompanyID   ContactID   PrimaryContact
1           285         0
2           375         0
3           761         0
4           1744        0
4           2301        0
6           335         0
6           4987        0
9           809         0
9           2119        0
10          99          0
11          99          0

In the above example CompanyID 4, 6, and 9 have multiple entries so I don't want to touch those. But 1, 2, 3, 10, and 11 have single entries so I want to set PrimaryContact to 1 for those rows.

How can this be done?

Upvotes: 1

Views: 3012

Answers (1)

jpw
jpw

Reputation: 44871

The syntax you want for MS SQL Server is this:

update contacts
set PrimaryContact = 1
where CompanyID in (
    select CompanyID
    from contacts
    group by CompanyID
    having count(distinct ContactID) = 1
)

The question was previously tagged as MySQL and the original answer below is adapted to that.

This is one way to do it:

update contacts as c
inner join 
(
    select CompanyID
    from contacts
    group by CompanyID
    having count(distinct ContactID) = 1
) t on c.CompanyID= t.CompanyID
set PrimaryContact = 1;

In the derived table you find the CompanyIDs that have one single ContactID. The result from that query is then joined with the table that should be updated.

Sample SQL Fiddle

Upvotes: 2

Related Questions