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
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
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 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply