0

I'm having some trouble to find an easy way to check if a column contains a certain value before updating the value in another column in a table row. For example, if I have the table StudentsParticipationInClass like below

    |  ID  |  Grade  |  State  |
    |  1   |   'U'   |'OnGoing'|
    |  2   |   '3'   | 'Done'  |

I want a constraint that tells the user that to be able to update the state to Done the Grade must be something else than 'U' or NULL.

Sam
  • 7,252
  • 16
  • 46
  • 65
ccb3
  • 39
  • 6
  • I suggest that you add some logic to your application code so that only records allowed to be updated be shown as such. – Dan Bracuk Oct 15 '13 at 16:25
  • I want it to be at database level so that if another program is developed in the future it does'nt have to add that logic into it as well. – ccb3 Oct 15 '13 at 16:30

1 Answers1

0

This query may help you:

UPDATE StudentsParticipationInClass
SET State = 'Done'
WHERE Grade <> 'U'
AND WHERE Grade IS NOT NULL
AND WHERE ID = 1

Azamat
  • 435
  • 5
  • 13
  • Is this SQL -> `Grade != 'U'` – Nadeem_MK Oct 15 '13 at 16:30
  • I don't want it to be in an update query, I would like it as a check constraint so you dont have to worry about it in every update statement created later on – ccb3 Oct 15 '13 at 16:33
  • 1
    @Nadeem_MK , in some versions of SQL `!=` [it works](http://www.w3schools.com/sql/sql_where.asp), but yeah, `<>` seems better. Edited my answer. – Azamat Oct 15 '13 at 16:41