CODEWITHSUNDEEP
phpmysql
Jasir
Jasir

Reputation: 67

Mysql select row with same value column

I have a student mark table with 4 question answers marked as 1,-1, and 0 means correct, incorrect, and not attended respectively. I am adding a table structure below. What I am looking for a MySQL query to update another column as selected or not selected based on how much 1 they got. So if my condition is a student which got at least 1 got will select then I can write UPDATE table SET selected = 1 WHERE total_mark > 0 (atleast one correct will get a mark of greater than 0).

No, I need at least 2 selected or 3 selected how will I write an update query.

enter image description here

Upvotes: 1

Views: 69

Answers (2)

forpas
forpas

Reputation: 164064

If you want at least 2 1s for the column selected to be 1, then:

UPDATE tablename 
SET selected = (m1 = 1) + (m2 = 1) + (m3 = 1) + (m4 = 1) >= 2

Each of the boolean expressions mx = 1 evaluates to 1 or 0 for true or false.

See the demo.
Results:

> id | name  | m1 | m2 | m3 | m4 | selected
> -: | :---- | -: | -: | -: | -: | -------:
>  1 | user1 |  1 |  1 |  1 |  1 |        1
>  2 | user2 |  1 | -1 | -1 | -1 |        0
>  3 | user3 |  1 |  1 |  0 |  0 |        1

Upvotes: 1

iguypouf
iguypouf

Reputation: 770

The best approach is to improve your relational model, by creating a USER_ANSWERS table, then you can simply count the answers having score = 1. Then you are not dependant of the number of questions you have in a survey.

If its not something you want to do, this query does the trick :

UPDATE TABLE SET selected = 1 WHERE GREATEST(0,m1) + GREATEST(0,m2) + GREATEST(0,m3) + GREATEST(0,m4) > 0

Upvotes: 1

Related Questions