Results 1 to 8 of 8
  1. #1
    Join Date
    Sep 2003
    Posts
    102

    Unanswered: Update one record & OUTPUT two records

    For a staging table

    Table TimeRange
    id binary(16),
    startTime datetime,
    endTime datetime,
    isValid tinyint

    There are two validation rules: starttime cannot be null, endTime cannot be null (assume that we cannot set the columns as NOT NULL).

    We would like to OUTPUT an error record for each validation error for every record in the TimeRange table.

    Would there be a single statement that could do this ? (ie. would UPDATE invalid record AND would OUTPUT two validation error records for a record that has startTime = NULL AND endTime = NULL)

    Something like:


    UPDATE TimeRange
    SET isValid = 0
    OUTPUT inserted.id,
    CASE WHEN inserted.startTime is NULL THEN inserted.startTime
    WHEN inserted.endTime is NULL THEN inserted.endTime
    END -- Needs to handle the case where both startTime and endTime are invalid
    INTO @InvalidRecords
    FROM (a SELECT stmt that is a table with a record for each validation error)


    MERGE does not have the functionality needed (inserting multiple records for every invalid record).

    Have not had success using a UNION ALL, as there is an error updating derived tables.

    Have not had success creating a JOIN statement.

    Thx!

  2. #2
    Join Date
    Jan 2013
    Posts
    354
    Provided Answers: 1
    But you are fine with (vague_start_timestamp > vague_end_timestamp) in your data?

    Rows are not records. Can you do the data cleaning outside the database?

  3. #3
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    UPDATE and OUTPUT are separate transactions, and data validation is a whole other ballgame.
    This is what stored procedures are for.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  4. #4
    Join Date
    Sep 2003
    Posts
    102
    Celko:

    Good catch with that validation. It is one of our validations; for simplicity's sake, I did not mention it.

    blindman:
    Do you mean since they are separate transactions, is is possible that the UPDATE could complete and the OUTPUT would fail and vice versa ? Therefore is not a good idea to use ?


    Everyone else:

    Anybody have any ideas on the original problem , I am asking less from a design or speed concert, but am just curious if it is technically possible to do.

  5. #5
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Mixing reporting functions with data changes has always been a receipe for disaster. In a few, rare cases the two functions work tolerably well together but those exceptions are few and far between.

    Do your reporting as one step, output the messages about the offenses that you find.

    Do your repairs as a completely separate step.

    It is possible to combine the two operations by creating a stored procedure or a client side routine. This is still a bad idea.

    It is possible to execute both operations within the context of a transaction. This is an exceedingly bad idea.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  6. #6
    Join Date
    Sep 2003
    Posts
    102
    Quote Originally Posted by Pat Phelan View Post
    Mixing reporting functions with data changes has always been a receipe for disaster. In a few, rare cases the two functions work tolerably well together but those exceptions are few and far between.

    Do your reporting as one step, output the messages about the offenses that you find.

    Do your repairs as a completely separate step.

    -PatP
    Does 'repairs' include validation modifications ?


    The purpose of our stored procedure is to:
    - invalidate all invalid staging records.
    - create 'Invalidation' records in a reporting table.


    You would suggest we would have two separate store procedures because the two purposes should not be grouped together ?

    We have combined these two parts together as there is not a way to indicate how many and which validation errors a row has. So essentially, we would be running the same queries twice (once to invalidate the staging record; and once to populate another table).

  7. #7
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I'll assume that you know much more about your data than what you've published in this thread. That means that you understand the data far better than we possibly could understand it, because you have much more information available about it.

    In general, mixing reporting with data changes is a bad idea. For example, mixing a financial report with a status update that the report reflects is a really bad choice because you can't report on a problem without destroying the data upon which that report is based. An example where this pairing of reporting with data change makes sense would be telco switch reporting, where the data itself is ephemeral and there is no practical way to report and repair separately.

    Without knowing exactly what you are doing and possibly even why you are doing it, there is no way for me to concretely judge whether it makes sense to do both operations in a single pass. Based on my past experience (which has been supported by nearly every data management professional that I've ever worked with), it is nearly always a bad plan to have a reporting process alter the data on which it is reporting in any way.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  8. #8
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Quote Originally Posted by ontheDB View Post
    blindman:
    Do you mean since they are separate transactions, is is possible that the UPDATE could complete and the OUTPUT would fail and vice versa ? Therefore is not a good idea to use ?
    No, I mean that since they are two separate transactions, there is no "single statement" that will do them both. You need to perform them as separate steps.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Posting Permissions

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