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

    One statement to do an Insert and Update

    I did a quick search and did not see anything in this forum specific to this problem:

    Problem: In one statement (or whatever is most efficient if not possible), how do I update the rows in table A (based on a where clause), and insert into table B, the data from those same number of rows.

    For example,

    Table A: columns: id , lastName, department, branch
    Table B: columns: id, errorMessage, lastName

    UPDATE table A by settting branch = 'ETC' where department IS NULL

    INSERT INTO table b the rows found in the above UPDATE statement


    note: 1.To find the rows UPDATED in the first statement, it is not sufficient to search by branch='ETC', as that will encompass more rows.
    2. I do not want to use triggers as these statements will be executed once in the entire process

    Thanks!
    Last edited by ontheDB; 01-23-13 at 13:49.

  2. #2
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,513
    You'll want to look into the OUTPUT clause.

  3. #3
    Join Date
    Sep 2003
    Posts
    102
    Quote Originally Posted by MCrowley View Post
    You'll want to look into the OUTPUT clause.
    Thanks!
    THat seems like it'll do the trick

  4. #4
    Join Date
    Sep 2003
    Posts
    102
    Another question:
    A new requirement is to conditionally Insert/output some of the records which were updated.

    I had tried adding a where clause or a condition statement with no luck.

    Something like:

    UPDATE c
    SET c.column = case when a.id is null then 1 else 2 end,
    OUTPUT
    CURRENT_TIMESTAMP, inserted.column
    where inserted.column = 1
    INTO Table b
    FROM table c join table a on (a.id = c.id)


    Thanks!

  5. #5
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    14,924
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  6. #6
    Join Date
    Jan 2013
    Posts
    306
    Am I missing something? The set of rows in Table Alpha is:

    CREATE VIEW Unknown_Department_Alphas
    AS
    SELECT *
    FROM Alpha -- wish we had a valid table!
    WHERE department_name IS NULL;

    Why would you fake a deck of punch cards or mag tape by materializing it in a redundant second table? This looks like you are trying to do an audit trail in the same SQL as your schema. NEVER do that! You will go to jail and it will not work.

    Auditing is always done at the boundary of the system, so that you can catch SELECT statements as requried by HIPPA, BASEL II, etc.

    Also that UPDATE ..FROM .. syntax is 1970's Sybase dialect; good SQL programmers do not use and are aware the cardinality problems. Might want to Google that.

  7. #7
    Join Date
    Sep 2003
    Posts
    102
    Thanks for the replies!

    Before we get off topic, here are some more specific details:

    Validation is performed on a column in Table A (against another lookup table)
    - if it is valid, the record in Table A is marked as such
    - if it is invalid, the record in Table B is marked as such and another record is inserted into Table B

    I am unsuccessful in using a MERGE statement (or UPDATE) to be in line with the above behaviour because the OUTPUT will contain both the valid and invalid records.

  8. #8
    Join Date
    Jan 2013
    Posts
    306
    >> Validation is performed on a column in Table A (against another look-up table) <<

    In a well-designed schema that is done either by a CHECK() or a REFERENCES constraint; we would see that if you had posted DDL, as per Basic Netiquette. The goal of RM is that the tables are always in a valid state (i.e all constraints are TRUE) at the end of each transaction.

    Rows are nothing like records. Would you like links to articles and books on this basic concept of RDBMS?

    >> - if it is valid, the record [sic] in Table A is marked as such
    if it is invalid, the record [sic] in Table B is marked as such and another record [sic] is inserted into Table B <<

    This is a narrative description of a punch card edit program. The good cards go to card sorter pocket A (vague generic name); the bad cards go to card sorter pocket B (second vague generic name). You still have a workflow model of data! This is not how we program in SQL.

    >> I am unsuccessful in using a MERGE statement (or UPDATE) to be in line with the above behavior because the OUTPUT will contain both the valid and invalid records [sic]. <<

    We have no DDL, no specs and a vague narrative. Why did you think anyone can write code from this? You show code where a magical generic “id” is NULL! It is impossible for an identifier in RDBMS by definition. The NULL department_something was perhaps bad design, but this is just wrong.

    You seem to have more problems than we can handle here in a forum.

  9. #9
    Join Date
    Sep 2003
    Posts
    102
    Quote Originally Posted by Celko View Post
    >> Validation is performed on a column in Table A (against another look-up table) <<

    In a well-designed schema that is done either by a CHECK() or a REFERENCES constraint; we would see that if you had posted DDL, as per Basic Netiquette. The goal of RM is that the tables are always in a valid state (i.e all constraints are TRUE) at the end of each transaction.

    Rows are nothing like records. Would you like links to articles and books on this basic concept of RDBMS?
    This is business rule validation on a Staging table.

    I see that you are the resident 'hall-monitor' and I respect your opinion; but to say rows are nothing like records is not completely true.. They are not the same thing; but they are related concepts.

    Quote Originally Posted by Celko View Post
    >> - if it is valid, the record [sic] in Table A is marked as such
    if it is invalid, the record [sic] in Table B is marked as such and another record [sic] is inserted into Table B <<

    This is a narrative description of a punch card edit program. The good cards go to card sorter pocket A (vague generic name); the bad cards go to card sorter pocket B (second vague generic name). You still have a workflow model of data! This is not how we program in SQL.
    To clarify:
    All the records stay in Table A.
    Table B has only a record for each validation error of a record in Table A.

    Unfortunately, I am not in a position to re-design; but I will bring up your points to those who are.

    Quote Originally Posted by Celko View Post
    >> I am unsuccessful in using a MERGE statement (or UPDATE) to be in line with the above behavior because the OUTPUT will contain both the valid and invalid records [sic]. <<

    We have no DDL, no specs and a vague narrative. Why did you think anyone can write code from this? You show code where a magical generic “id” is NULL! It is impossible for an identifier in RDBMS by definition. The NULL department_something was perhaps bad design, but this is just wrong.

    You seem to have more problems than we can handle here in a forum.
    Unfortunately, I do not post DDL (as do most ppl) as it would be overkill for my question.
    I am not asking for a specific solution.

    Currently, the question is: does anyone know if one can conditionally select the records from an OUTPUT clause in an UPDATE statement ?

  10. #10
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,566
    Because you are dealing with two different tables, you can't do this in a MERGE statement, or any other single sql statement.

    The only way to achieve this is a single database call would be to have a trigger on table A which inserts data into table B. This is a pretty standard structure for archive tables, which sounds like the scenario you are dealing with.
    Explain in more detail why you can't use triggers? Triggers can (and should) handle multi-record transactions.
    If it's not practically useful, then it's practically useless.

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

  11. #11
    Join Date
    Sep 2003
    Posts
    102
    Quote Originally Posted by blindman View Post
    Because you are dealing with two different tables, you can't do this in a MERGE statement, or any other single sql statement.

    The only way to achieve this is a single database call would be to have a trigger on table A which inserts data into table B. This is a pretty standard structure for archive tables, which sounds like the scenario you are dealing with.
    Explain in more detail why you can't use triggers? Triggers can (and should) handle multi-record transactions.
    Thinking it over, a Trigger would solve this issue.
    I was initially told to rule out Triggers as part of the design; but I believe this would be acceptable.

    Thanks!

  12. #12
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,566
    Quote Originally Posted by ontheDB View Post
    I was initially told to rule out Triggers as part of the design; but I believe this would be acceptable.
    No doubt you were told this by someone who does not know how to properly write triggers....
    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
  •