November 9, 2004 at 2:34 pm
Hello and thanks in advance for the help.
What I am trying to accoomplish in the UPDATE query below is to update only the fields that have that meet a criteria.
My criteria is to update only the fields do not have a value. If the field has a value than skip to the next record. My query below updates all my records even if a value exists. What am I doing wrong?
IF EXISTS(SELECT F00069, F00026, F01298, F01299, F01300
FROM patient INNER JOIN
tumor ON patient.UK = tumor.FK1 INNER JOIN
followup ON tumor.FK1 = followup.FK1 INNER JOIN
hospital ON tumor.UK = hospital.FK2
WHERE (hospital.F00026 = '0' OR hospital.F00026 = '1' OR hospital.F00026 = '2')
AND patient.F00069 = '1'
OR followup.F01298 = ' ' AND followup.F01299 = ' ' AND followup.F01300 = ' ')
BEGIN
UPDATE followup SET followup.F01298 = 'C', followup.F01299 = 'C', followup.F01300 = 'C'
END
ELSE
PRINT 'Nothing to Update'
Thank you...
November 9, 2004 at 2:48 pm
I think if you put parens around your OR statement, it may look better.
IF EXISTS(SELECT F00069, F00026, F01298, F01299, F01300
FROM patient INNER JOIN
tumor ON patient.UK = tumor.FK1 INNER JOIN
followup ON tumor.FK1 = followup.FK1 INNER JOIN
hospital ON tumor.UK = hospital.FK2
WHERE (hospital.F00026 = '0' OR hospital.F00026 = '1' OR hospital.F00026 = '2')
AND patient.F00069 = '1'
OR (followup.F01298 = ' ' AND followup.F01299 = ' ' AND followup.F01300 = ' '))
BEGIN
UPDATE followup SET followup.F01298 = 'C', followup.F01299 = 'C', followup.F01300 = 'C'
END
ELSE
PRINT 'Nothing to Update'
It looks like you are saying the hospital.F00026 can be 0, 1, or 2 AND the Patient.F00069 must be 1 OR the other three fields are empty. If Patient.F00069 is 1, you have fulfilled your criteria.
You may want to change the OR to an AND.
I wasn't born stupid - I had to study.
November 9, 2004 at 2:53 pm
Thanks for the reply. I had tried replacing the OR with an AND, and I still get the same results.
IF EXISTS(SELECT F00069, F00026, F01298, F01299, F01300
FROM patient INNER JOIN
tumor ON patient.UK = tumor.FK1 INNER JOIN
followup ON tumor.FK1 = followup.FK1 INNER JOIN
hospital ON tumor.UK = hospital.FK2
WHERE (hospital.F00026 = '0' OR hospital.F00026 = '1' OR hospital.F00026 = '2')
AND patient.F00069 = '1' AND followup.F01298 = ' ' AND followup.F01299 = ' ' AND followup.F01300 = ' ')
BEGIN
UPDATE followup SET followup.F01298 = 'C', followup.F01299 = 'C', followup.F01300 = 'C'
END
ELSE
PRINT 'Nothing to Update'
Thanks again...
November 9, 2004 at 3:00 pm
..I agree, swap the or for an and....I rewrote the code below and tested it with a positive outcome.
IF EXISTS(SELECT patient.F00069, patient.F00026, patient.F01298, patient.F01299, patient.F01300
FROM patient INNER JOIN
tumor ON patient.UK = tumor.FK1 INNER JOIN
followup ON tumor.FK1 = followup.FK1 INNER JOIN
hospital ON tumor.UK = hospital.FK2
WHERE (hospital.F00026 = '0' OR hospital.F00026 = '1' OR hospital.F00026 = '2')
AND patient.F00069 = '1'
AND followup.F01298 = ' ' AND followup.F01299 = ' ' AND followup.F01300 = ' ')
BEGIN
UPDATE followup SET followup.F01298 = 'C', followup.F01299 = 'C', followup.F01300 = 'C'
END
ELSE
PRINT 'Nothing to Update'
November 9, 2004 at 3:13 pm
Sorry guys but it does not work. The update query updates all fields. Even the fields that have values. What I am trying to accomplish is to update only the fields that do not have values in them.
IF EXISTS(SELECT patient.F00069, hospital.F00026, followup.F01298, followup.F01299, followup.F01300
FROM patient INNER JOIN
tumor ON patient.UK = tumor.FK1 INNER JOIN
followup ON tumor.FK1 = followup.FK1 INNER JOIN
hospital ON tumor.UK = hospital.FK2
WHERE (hospital.F00026 = '0' OR hospital.F00026 = '1' OR hospital.F00026 = '2')
AND patient.F00069 = '1'
AND followup.F01298 = ' ' AND followup.F01299 = ' ' AND followup.F01300 = ' ')
BEGIN
UPDATE followup SET followup.F01298 = 'C', followup.F01299 = 'C', followup.F01300 = 'C'
END
ELSE
PRINT 'Nothing to Update'
Thanks for all your help...
November 9, 2004 at 10:19 pm
Your update query does not contain a WHERE clause. So what you are saying is
"If there are any records returned by the SELECT ... query, execute the following update query:
UPDATE followup SET followup.F01298 = 'C', followup.F01299 = 'C', followup.F01300 = 'C'" - which of course updates all records in followup.
Obviously, this is not your aim. You need to recast your SQL along the following lines:
update followup
set ...
from ...
where ... (and this is where you select the records that you would like to update)
Regards
Phil
November 10, 2004 at 1:16 am
Enter CASE, your new friend...
UPDATE myTable SET myColumn = CASE WHEN myColumn = '' THEN 'C' ELSE myColumn END WHERE ...
So in your case it may be somthing like:
UPDATE followup SET followup.F01298 = CASE WHEN followup.F01298 = '' THEN 'C' ELSE followup.F01298 END, SET followup.F01299 = CASE WHEN followup.F01299 = '' THEN 'C' ELSE followup.F01299 END, SET followup.F01300 = CASE WHEN followup.F01300 = '' THEN 'C' ELSE followup.F01300 END FROM patient INNER JOIN tumor ON patient.UK = tumor.FK1 INNER JOIN followup ON tumor.FK1 = followup.FK1 INNER JOIN hospital ON tumor.UK = hospital.FK2 WHERE (hospital.F00026 = '0' OR hospital.F00026 = '1' OR hospital.F00026 = '2') AND patient.F00069 = '1' AND followup.F01298 = ' ' AND followup.F01299 = ' ' AND followup.F01300 = ' ')
Julian Kuiters
juliankuiters.id.au
November 10, 2004 at 12:44 pm
Thanks to all of you that contributed. I used the CASE statement and it worked according to my criteria.
Thank you...
Viewing 8 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply