Marking a record based on data in it's "Many" relationships.

[Nkuha]

I have a one to many relationship. I want to count all instances of "true" in a field in the "Many" table, and if any are found I want to mark another binary field "true" in the "One" table. If none are found, I want to mark the field in the "One" table as false. Is this possible??

 

[RickCole]

If I understand your question correctly something like this should do the trick:

UPDATE ONE_TABLE
SET ONE_TABLE.BINARY_FIELD = 'TRUE'
FROM ONE_TABLE, MANY_TABLE
WHERE ONE_TABLE.PK_ID = MANY_TABLE.PK_ID AND (SELECT COUNT (*) FROM ONE_TABLE, MANY_TABLE
WHERE ONE_TABLE.PK_ID = MANY_TABLE.PK_ID AND MANY_TABLE.BINARY_FIELD = 'TRUE') > 0

Rick.

 

[Nkuha]

Thank you. This code looks logical, but I'm having trouble getting it to work. I get the error:

"syntax error converting the varchar value 'true' to a column of data type bit."

I tried replacing "true" with -1 and "false" with 0 in my code to no avail. Here is my actual code:

cnn.Execute ("Update cases SET cases.manflag = 'true' FROM cases, casenotes WHERE cases.caid = casenotes.caid and (select count (*) from cases, casenotes where cases.caid = casenotes.caid and casenotes.flagger = 'true') > 0&quot

cnn.Execute ("Update cases SET cases.manflag = 'false' FROM cases, casenotes WHERE cases.caid = casenotes.caid and (select count (*) from cases, casenotes where cases.caid = casenotes.caid and casenotes.flagger = 'true') = 0&quot

 

[RickCole]

Sorry I was being a bit lazy with my example as I couldn't recall weather 0 or 1 was equal to true in a bit field.

In SQL Server a bit field of 0 is false and 1 is true. Try the following.

cnn.Execute ("Update cases SET cases.manflag = 1 FROM cases, casenotes WHERE cases.caid = casenotes.caid and (select count (*) from cases, casenotes where cases.caid = casenotes.caid and casenotes.flagger = 1) > 0&quot

cnn.Execute ("Update cases SET cases.manflag = 0 FROM cases, casenotes WHERE cases.caid = casenotes.caid and (select count (*) from cases, casenotes where cases.caid = casenotes.caid and casenotes.flagger = 1) = 0&quot

Rick.

 

[lestermadoc]

ok, it accepts this code, but if there is only a single flag = true in the notes table, it will mark every single cases manflag = true.

??

 

[lestermadoc]

Does anyone know how I can get this to work?