Results 1 to 6 of 6
  1. #1
    Join Date
    Jan 2006
    Posts
    9

    Unanswered: Update Query Help!

    The query below executes successfully but it updates all the records. I want to update only the parameters on the 'where' clause. What am I missing?

    Thanks.

    UPDATE tblIncident
    SET UnprocessedPoints = 6
    FROM [tblIncident] as Incident, [tblperformedaudit] as Audit
    where ((incident.databaseid = audit.databaseid)
    and (audit.auditnbr = 2)
    AND (audit.doctype = 'CLAIM')OR(audit.doctype = 'TAR') OR (audit.doctype = 'CIF')
    AND (audit.monthofsample = 'oct')
    And (Incident.Status <> 'Removed' )
    AND (Incident.typeofFindings = 'Error')
    AND (Incident.errorID = 'Unprocessed'))

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    A pair of parentheses?
    Code:
    UPDATE tblIncident
       SET UnprocessedPoints = 6
       FROM [tblIncident] as Incident, [tblperformedaudit] as Audit
       where ((incident.databaseid = audit.databaseid) 
          and (audit.auditnbr = 2) 
          AND ( (audit.doctype = 'CLAIM')
             OR (audit.doctype = 'TAR')
             OR (audit.doctype = 'CIF') )
          AND (audit.monthofsample = 'oct') 
          And (Incident.Status <> 'Removed' ) 
          AND (Incident.typeofFindings = 'Error') 
          AND (Incident.errorID = 'Unprocessed'))
    -PatP

  3. #3
    Join Date
    Jan 2006
    Posts
    9

    I added the parenthesis....

    and now it's not updating any records....

  4. #4
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    This is the same query rewritten in another way; what does it say? BTW, probably it doesn't mean much, but - did you take care of all that upper and lower and initial caps stuff ('TAR' vs. 'oct' vs. 'Removed')?
    Code:
    UPDATE tblincident i SET
      i.unprocessedpoints = 6
      WHERE i.databaseid IN (SELECT a.databaseid
                             FROM tblperfomedaudit a
                             WHERE a.auditnbr = 2
                               AND a.doctype IN ('CLAIM', 'TAR', 'CIF') 
                               AND a.monthofsample = 'oct'
                            )
        AND i.status <> 'Removed'
        AND i.typeoffindings = 'Error'
        AND i.errorid = 'Unprocessed'

  5. #5
    Join Date
    Jan 2006
    Posts
    9

    Still getting errors

    I tried running the query and am getting this error.

    Server: Msg 170, Level 15, State 1, Line 1
    Line 1: Incorrect syntax near 'i'.
    Server: Msg 156, Level 15, State 1, Line 9
    Incorrect syntax near the keyword 'AND'.

    Thank you.

  6. #6
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Cool. As long as my query would probably do something on Oracle, it must be a complete nonsense on MS SQL Server. At the moment I wrote it I didn't know which DB you are using and, somehow, hoped that it might do some good.

    Sorry for wasting your time.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •