MySQL: Is there an operator to identify unique values?

fdbelqdn 于 2022-12-22 发布在 Mysql

关注(0)|答案(1)|浏览(121)

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'.

mysql

来源：https://stackoverflow.com/questions/74843170/mysql-is-there-an-operator-to-identify-unique-values

1条答案

按热度按时间

o0lyfsai1#

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

赞(0）回复(0）举报 2022-12-22

我来回答

MySQL: Is there an operator to identify unique values?

1条答案

相关问题

热门标签

最新问答