Using "select count"

[lestermadoc]

I have a one to many relationship between "cases" and "casenotes". Wherever there is a "flagger = true" in "casenotes" I want to mark the record in "cases" as "true" in the "manflag" field.

The problem with my code is that if there is only one "flagger=true" in one of the "casenotes" records, then it marks every single "cases" record as "manflag=true". My code is listed below.

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

 

[JayKusch]

Would this work?

Update cases
SET cases.manflag = 'true'
FROM cases, casenotes
WHERE cases.caid = casenotes.caid
and casenotes.flagger = 'true'

 

[aalmeida]

have you tryed the following:
cnn.Execute ("
Update cases SET cases.manflag = 'true'
FROM cases inner join casenotes on (cases.caid = casenotes.caid)
WHERE casenotes.flagger = 'true'&quot

let me know
AL Almeida
NT/DB Admin
"May all those that come behind us, find us faithfull"

 

[lestermadoc]

Well, I'm trying to make it so that I can set "cases.manflag = false" where there is no match in "casenotes.flagger".

This is actually where my problem is - I need to know if there are no "casenotes.flagger = true" for a "cases" record, and mark the "cases" record "manflag = false".

Here is my code for that:

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

 

[lestermadoc]

sorry for the confusion - I also need to mark the proper record as true when there is a match, so your examples do work for that purpose. Thanks.

Can you help me with the problem of setting cases.manflag = false where there is no record in casenotes where flagger = true?

 

[aalmeida]

what about something like this:
cnn.Execute ("
Update cases SET cases.manflag = case when casenotes.flagger = 'true'then 'true' else 'false' end
FROM cases inner join casenotes on (cases.caid = casenotes.caid)
&quot
let me know AL Almeida
NT/DB Admin
"May all those that come behind us, find us faithfull"

 

[lestermadoc]

I get "incorrect syntax near the keyword 'then'".

I had to replace "True" and "false" with their integer values: (-1 = true and 0 = false if I remember correctly)

cnn.Execute ("Update cases SET cases.manflag = -1 when casenotes.flagger = -1 then -1 else 0 end FROM cases inner join casenotes on (cases.caid = casenotes.caid)&quot

 

[aalmeida]

give it a try and let me know AL Almeida
NT/DB Admin
"May all those that come behind us, find us faithfull"

 

[lestermadoc]

well, I tried it before I sent my last message - I get that error after I changed the "true" and "false" to "-1" and "0"

 

[aalmeida]

put a space between the " 'true' " and the "then" what is the data type of the columns? AL Almeida
NT/DB Admin
"May all those that come behind us, find us faithfull"

 

[lestermadoc]

it does not accept the words 'true' or 'false', but it will accept any single digit. It sees a -1 as true, and 0 as false.

I tried putting the space in regardless, and I still get a wrong data type error.

 

[JayKusch]

Maybe an easier way would be to set the ones that do match to -1/'yes' and the go back to you table and set all the ones that are NOT -1/'yes' to 0/'false'

 

[lestermadoc]

I don't understand.

The fields of "flagger" and "manflag" are both of the data type "bit", so if you set flagger or manflag to -1, the database will accept that as a "true", and if you set either fields to "0" then the database will accept is as a "false".