How can I insert a value in a determined column with and statment?
Please someone could fix that question for me? don't know how to ask =/
I have 2 main columns:
CONTEST(PK) and RESULT and other column named RESULTCHECK
I need to insert the value "1" in the column RESULTCHECK where the RESULT has some statement like.
for example
CONTEST RESULT
1 1,2,3,4,5
2 2,3,4,5,6
I want something like INSERT INTO RESULTCHECK VALUES 1 WHERE RESULT LIKE '%2,3%'
how can I do that?
and in the end 开发者_运维问答I have the currently result:
CONTEST RESULT RESULTCHECK
1 1,2,3,4,5 1
2 2,3,4,5,6 1
3 5,6,7,8,9 NULL
You should listen to @teresko's advice about modelling your data correctly (apart from the English bit) , but otherwise the answer is
update TABLENAME set RESULTCHECK = 1 where RESULT like "%2,3%"
It's because your DB structure is WRONG.
There is this thing , called many-to-many relation and junction tables.
Basically, what you need is a
CREATE TABLE ContestResults(
contest_id INT NOT NULL,
result_id INT NOT NULL,
PRIMARY KEY ( contest_id , result_id ),
FOREIGN KEY ( contest_id ) REFERENCES Contests( contest_is ) ,
FOREIGN KEY ( result_id ) REFERENCES Results( result_is )
)
Where you keep data about the relationship between many contests and many possible results.
P.S. and, please, use english names for the things in your code.
If you use Firebird 2.5, I think you can also use SIMILAR TO for regular expression
A new SIMILAR TO predicate is introduced to support regular expressions. The predicate's function is to verify whether a given SQL-standard regular expression matches a string argument. It is valid in any context that accepts Boolean expressions, such as WHERE clauses, CHECK constraints and PSQL IF() tests
精彩评论