CODEWITHSUNDEEP
mysqlsqlsyntaxoperators
boleroemoji
boleroemoji

Reputation: 11

MySQL: Is there an operator to identify unique values?

I want to update the column 'status' in a dataframe 'Enrollments' that references another 'visitID' column, but I want the values within 'status' to be as follows:

When there are 2 or more Id's that are the same in 'visitID' column - set that value in 'status' as 'returning' and when there is only one unique value in 'visitID' - set that respective value in status as 'new'.

Here is what I tried:

UPDATE Enrollments
SET Status = (CASE WHEN VisitID IS UNIQUE THEN 'New'  ELSE 'Returning' END)

I am receiving this error message:

Incorrect syntax near the keyword 'UNIQUE'.

Upvotes: 0

Views: 58

Answers (1)

nbk
nbk

Reputation: 49385

You could make it like this

As you can see the subquery count the occurences of visitID and depending on the number sets the status

CREATE tABLe Enrollments (VisitID int, Status varchar(10))

INSERT INTO Enrollments values (1,NULL),(1,NULL),(2,NULL)

Records: 3  Duplicates: 0  Warnings: 0

UPDATE Enrollments
SET Status = CASE WHEN (SELECT COUNT(*) FROM (SELECT * FROM Enrollments) e1 WHERE e1.VisitID = Enrollments.VisitID) = 1  THEN 'New'  ELSE 'Returning' END


Rows matched: 3  Changed: 3  Warnings: 0

SELECT * FROM Enrollments
VisitID Status
1 Returning
1 Returning
2 New

fiddle

Upvotes: 1

Related Questions