[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 169
  • Last Modified:

Update dpending on combination of two columns

Hi
I have a table UserCompany:
CompanID
UserID
DefaultCompany

A user can have serveral rows in the table, connected to several Companies.
I want to update the row with the min(CompanyID) and set defauly = true:

I can select the rows:

Select min(CompanyID) as comp,UserID
from UserCompany uc
where DefaultCompany <> 1
group by UserID

But can seem to figure out how to update the same rows?




0
staleb
Asked:
staleb
1 Solution
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
here we go:
UPDATE uc
   SET DefaultCompany = 1
FROM UserCompany uc
 WHERE uc.CompanyID = ( SELECT MIN(i.CompanyID)
                 FROM UserCompany i
                 WHERE i.UserID = uc.UserID
                )

Open in new window

0
 
stalebAuthor Commented:
Thanks

I was so close, but still so far away
0

Featured Post

Learn to develop an Android App

Want to increase your earning potential in 2018? Pad your resume with app building experience. Learn how with this hands-on course.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now