Help with UPDATE query

  • 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...

  • 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.

  • 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...

  • ..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'

  • 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...

  • 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


  • 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

  • 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