SQLite – How to Create an ‘int’ Column Forced Between 0 and 3

constraintsqliteupdate

I would like to create a table with one column that would be of type "integer", and locked between 0 and 3. Is this possible?

My goal would be to have this column so that I could just execute a increment or decrement command, to increment or decrement the value in the database.

Example

If value in DB = 0
I execute something like UPDATE xxx SET yyy = yyy-- WHERE zzz
Value would still be 0

If value in DB = 3
I execute something like UPDATE xxx SET yyy=yyy++ WHERE zzz
Value would still be 3

If value in DB = 3
I execute something like UPDATE xxx SET yyy=yyy-- WHERE zzz
Value would still be 2

If value in DB = 1
I execute something like UPDATE xxx SET yyy=yyy-- WHERE zzz
Value would still be 0

Best Answer

thank you for your answers. I am using SQLite3 local on my laptop for testing purpose, I am not sure what the "real" database will be. So for that reason and because it does not seem easy to restrict values in a certain range, I have solved my problem in the software itself.

Using the "AND" condition to check if values are in the interval I want: "UPDATE xxx SET xxx WHERE xx AND xx"

Thank you all.

Related Solutions

Mongodb update conditional set operator ($set only when it is insert )

It will work for conditional $set update operation

http://docs.mongodb.org/manual/reference/operator/update/setOnInsert/

db.products.update(
  { _id: 1 },
  {
    $set: { item: "apple" },
    $setOnInsert: { defaultQty: 100 }
  },
  {upsert: true }
)

Here setOnInsert will set defaultQty only when a new record is inserted. Both $set and $setOnInsert can be used together.

MySQL – How to Update Multiple Rows with Different Values Using a CSV File

-- Create some working space:
CREATE TEMPORARY TABLE t (
    productname ...,
    text ...
);
-- copy all the .CSV into it:
LOAD DATA INFILE INTO t ... .csv ...;
-- do the Update en masse:
UPDATE ForgeRock
    JOIN t ON t.productname = ForgeRock.productname
    SET ForgeRock.text = t.text;

Assuming that nothing changes from run to run, exactly the same statements can be reused.

If you also need to insert "new" data, see IODKU.

Example

Best Answer

Related Solutions

Mongodb update conditional set operator ($set only when it is insert )

MySQL – How to Update Multiple Rows with Different Values Using a CSV File

Related Question