none
Select and update statement in T-Sql Transaction

    Question

  • Hi all

    I have some little problem.. quite easy.. :-)

    I have a table with 1000 rows, with columns
    Status int
    TransactionID int identity..
    column.....N

    I need to do sql select " For XML AUTO, BINARY BASE64"

    <<Start Sql transaction>>
    1: Select all rows with status 5 -->> For XML AUTO, BINARY BASE64"

    2: Update all the selected rows with status 10.

    <<commit Sql transaction>>

    Your help will be appreicated.

    Regards


    AKE
    Thursday, October 15, 2009 3:20 PM

Answers

  • ;WITH cte AS
    (
    SELECT TOP 10 Status FROM Table WHERE Status = 5
    )
    UPDATE cte SET Status = 10
    Abdallah, PMP, MCTS
    Friday, October 16, 2009 11:02 AM

All replies

  • But what is the problem?  Do you not know how to write the SQL?

    The update seems easy:

    UPDATE Table
      SET status = 10
     WHERE status = 5


    Phil Brammer | http://www.ssistalk.com | Twitter: http://twitter.com/PhilBrammer
    Thursday, October 15, 2009 3:29 PM
    Moderator
  • BEGIN TRAN

    ;WITH cte AS
    (
    SELECT Status FROM Table WHERE Status = 5
    )
    UPDATE cte SET Status = 10

    COMMIT TRAN
    Abdallah, PMP, MCTS
    • Marked as answer by Akaschmid Friday, October 16, 2009 6:40 AM
    • Unmarked as answer by Akaschmid Friday, October 16, 2009 6:46 AM
    Thursday, October 15, 2009 3:30 PM
  • Hey Abdshall- i'm not sure there is any need for the CTE on this occasion. A simple update on Table as Phil has shown would suffice.
    every day is a school day
    Thursday, October 15, 2009 3:36 PM
    Moderator
  • BEGIN TRAN

    ;WITH cte AS
    (
    SELECT Status FROM Table WHERE Status = 5
    )
    UPDATE cte SET Status = 10

    COMMIT TRAN
    Abdallah, PMP, MCTS

    Could you explain how this statement differs from the one posted by Phil (other than the explicit transaction), and what does it bring new to solving the problem?

    Thanks,
    AMB
    Thursday, October 15, 2009 3:37 PM
    Moderator
  • Hey Abdshall- i'm not sure there is any need for the CTE on this occasion. A simple update on Table as Phil has shown would suffice.
    every day is a school day

    Was this in his requirement?

    1: Select all rows with status 5
    Abdallah, PMP, MCTS
    Thursday, October 15, 2009 3:38 PM
  • Hey Abdshall- i'm not sure there is any need for the CTE on this occasion. A simple update on Table as Phil has shown would suffice.
    every day is a school day

    The following was in his requirement

    1: Select all rows with status 5
    Abdallah, PMP, MCTS

    Do you think that using a CTE just for that will make any difference than using a "where" clause in the "update" statement?


    AMB
    Thursday, October 15, 2009 3:41 PM
    Moderator
  • I'm guessing here, but i think the OP actually just meant update all rows with a status of 5. I could be wrong though. No matter, he should have his answer anyhow.
    every day is a school day
    Thursday, October 15, 2009 3:44 PM
    Moderator
  • Hey Abdshall- i'm not sure there is any need for the CTE on this occasion. A simple update on Table as Phil has shown would suffice.
    every day is a school day

    The following was in his requirement

    1: Select all rows with status 5
    Abdallah, PMP, MCTS

    Do you think that using a CTE just for that will make any difference than using a "where" clause in the "update" statement?


    AMB

    Not really. The only reason I put it is because he explained what he is looking for and I just tried to provide a solution for what he is looking for

    <<Start Sql transaction>>
    1: Select all rows with status 5 -->> For XML AUTO, BINARY BASE64"

    2: Update all the selected rows with status 10.

    <<commit Sql transaction>>

    I thought he wants to see the results and that's why he asked for a SELECT before he does the UPDATE and then COMMIT the transaction.
    Did I commit a crime? :)
    Abdallah, PMP, MCTS
    Thursday, October 15, 2009 3:45 PM
  • Hey Abdshall- i'm not sure there is any need for the CTE on this occasion. A simple update on Table as Phil has shown would suffice.
    every day is a school day

    The following was in his requirement

    1: Select all rows with status 5
    Abdallah, PMP, MCTS

    Do you think that using a CTE just for that will make any difference than using a "where" clause in the "update" statement?


    AMB

    Not really. The only reason I put it is because he explained what he is looking for and I just tried to provide a solution for what he is looking for

    <<Start Sql transaction>>
    1: Select all rows with status 5 -->> For XML AUTO, BINARY BASE64"

    2: Update all the selected rows with status 10.

    <<commit Sql transaction>>

    I thought he wants to see the results and that's why he asked for a SELECT before he does the UPDATE and then COMMIT the transaction.
    Did I commit a crime? :)
    Abdallah, PMP, MCTS

    No, no crime, but the results aren't returned to the screen in your example, so it is just a regular update, which can be written as I had done already.
    Phil Brammer | http://www.ssistalk.com | Twitter: http://twitter.com/PhilBrammer
    Thursday, October 15, 2009 3:50 PM
    Moderator
  • You can select all rows with status 5 in table variable or temp table first, then select from it using FOR XML and also you may use it in UPDATE (though direct update would be quicker, I guess).

    In other words, the simplest solution would be doing both statements separately without trying to use a temp table / table variable, IMHO.
    Premature optimization is the root of all evil in programming.

    Donald Knuth, repeating C. A. R. Hoare

    My blog
  • Hey Abdshall- i'm not sure there is any need for the CTE on this occasion. A simple update on Table as Phil has shown would suffice.
    every day is a school day

    The following was in his requirement

    1: Select all rows with status 5
    Abdallah, PMP, MCTS

    Do you think that using a CTE just for that will make any difference than using a "where" clause in the "update" statement?


    AMB

    Not really. The only reason I put it is because he explained what he is looking for and I just tried to provide a solution for what he is looking for

    <<Start Sql transaction>>
    1: Select all rows with status 5 -->> For XML AUTO, BINARY BASE64"

    2: Update all the selected rows with status 10.

    <<commit Sql transaction>>

    I thought he wants to see the results and that's why he asked for a SELECT before he does the UPDATE and then COMMIT the transaction.
    Did I commit a crime? :)
    Abdallah, PMP, MCTS

    No, no crime, but the results aren't returned to the screen in your example, so it is just a regular update, which can be written as I had done already.
    Phil Brammer | http://www.ssistalk.com | Twitter: http://twitter.com/PhilBrammer
    Ok.
    @OP, you are under no obligation to use my code. Choose the one that fits your need, you just know how to use Common Table Expressions for UPDATE in case you don't.

    Moderators, both methods have the same Execution Plan, correct?
    Abdallah, PMP, MCTS
    Thursday, October 15, 2009 4:04 PM
  • Hi all

    Thanks for your inputs..

    it is interesting to read your inputs/discussion.

    I believe there are days one wake up .. cannot be able write  his/her name :-)

    Yesterday was one of them ...

    Best regards

    Aka
    Msc(BIS), Bsc(Hons)(IT & Computing) Mcse, Mcsd, Mct, Oracle Certified Associate, SAP Certified Solution Architect ERP  :-)
    AKE
    Friday, October 16, 2009 6:21 AM
  • Hi all

    Now I realized what made me to ask the question..

    I have

     

    <<Start Sql transaction>>
    1: Select TOP 10 rows with status 5 -->> For XML AUTO, BINARY BASE64"

    2: Update all the selected rows with status 10.

    <<commit Sql transaction>>

    Your help will be appreicated.


    AKE
    Friday, October 16, 2009 6:46 AM
  • Hi

    When I do select TOP 10

    I get the top 10 rows



    UPDATE Table
      SET status = 10
     WHERE status = 5


    all the rows are updated that have status 10,
    I need only to update the top 10 selected rows.. and not all the rows  :-)

    May be I should employ  CURSOR to solve the problem


    AKE
    Friday, October 16, 2009 6:48 AM
  • If you are using SQL Server 2008, you can utilize COMPOSABLE DML.


    select *
     from (
    UPDATE Table1
    SET Status = 10
    WHERE Status = 5
    OUTPUT deleted.*
    ) AS d
    For XML AUTO, BINARY BASE64
    Friday, October 16, 2009 7:08 AM
  • ;WITH cte AS
    (
    SELECT TOP 10 Status FROM Table WHERE Status = 5
    )
    UPDATE cte SET Status = 10
    Abdallah, PMP, MCTS
    Friday, October 16, 2009 11:02 AM
  • If you are using SQL Server 2008, you can utilize COMPOSABLE DML.


    select *
     from (
    UPDATE Table1
    SET Status = 10
    WHERE Status = 5
    OUTPUT deleted.*
    ) AS d
    For XML AUTO, BINARY BASE64

    I think you can use it if you are inserting the final result.

    use tempdb;
    go
    declare @t table(c1 int);
    
    insert into @t default values;
    
    -- this will fail
    select *
    from (
    update @t
    set c1 = 1
    output deleted.c1
    ) as T;
    go
    declare @t table(c1 int);
    declare @t2 table(c1 int);
    
    insert into @t default values;
    
    insert into @t2(c1)
    select *
    from (
    update @t
    set c1 = 1
    output deleted.c1
    ) as T;
    go

    AMB
    Friday, October 16, 2009 10:36 PM
    Moderator
  • ;WITH cte AS
    (
    SELECT TOP 10 Status FROM Table WHERE Status = 5
    )
    UPDATE cte SET Status = 10
    Abdallah, PMP, MCTS

    Using "top" clause or keyword without an "order by" clause is non-derterministic. If you are using SS 2005 / 2008, then you can accomplish the same using:

    update top (10) T
    set status = 10
    where status = 5;

    To be deterministic, then use:

    ;with r_set as (
    select top 10 pk, status
    from T
    where status = 5
    order by pk
    )
    update r_set
    set status = 10;


    AMB
    Saturday, October 17, 2009 4:11 PM
    Moderator
  • ;WITH cte AS
    (
    SELECT TOP 10 Status FROM Table WHERE Status = 5
    )
    UPDATE cte SET Status = 10
    Abdallah, PMP, MCTS

    Using "top" clause or keyword without an "order by" clause is non-derterministic. If you are using SS 2005 / 2008, then you can accomplish the same using:

    update top (10) T
    set status = 10
    where status = 5;

    To be deterministic, then use:

    ;with r_set as (
    select top 10 pk, status
    from T
    where status = 5
    order by pk
    )
    update r_set
    set status = 10;


    AMB

    Unless there is an index on an ID column, correct?
    Abdallah, PMP, MCTS
    Saturday, October 17, 2009 4:15 PM
  • ;WITH cte AS
    (
    SELECT TOP 10 Status FROM Table WHERE Status = 5
    )
    UPDATE cte SET Status = 10
    Abdallah, PMP, MCTS

    Using "top" clause or keyword without an "order by" clause is non-derterministic. If you are using SS 2005 / 2008, then you can accomplish the same using:

    update top (10) T
    set status = 10
    where status = 5;

    To be deterministic, then use:

    ;with r_set as (
    select top 10 pk, status
    from T
    where status = 5
    order by pk
    )
    update r_set
    set status = 10;


    AMB

    Unless there is an index on an ID column, correct?
    Abdallah, PMP, MCTS

    If somebody drop the index or alter the index, then what?

    if you use "order by" clause, then the result will be the same no matter how many time you run the select statement with the same group of data. The columns in the "order by" clause should identify uniquely each row in the set, if not you could get ties and the statement becomes un-deterministic unless you add another column to break the ties.


    AMB
    Saturday, October 17, 2009 4:25 PM
    Moderator
  • ;WITH cte AS
    (
    SELECT TOP 10 Status FROM Table WHERE Status = 5
    )
    UPDATE cte SET Status = 10
    Abdallah, PMP, MCTS

    Using "top" clause or keyword without an "order by" clause is non-derterministic. If you are using SS 2005 / 2008, then you can accomplish the same using:

    update top (10) T
    set status = 10
    where status = 5;

    To be deterministic, then use:

    ;with r_set as (
    select top 10 pk, status
    from T
    where status = 5
    order by pk
    )
    update r_set
    set status = 10;


    AMB

    Unless there is an index on an ID column, correct?
    Abdallah, PMP, MCTS

    If somebody drop the index or alter the index, then what?

    if you use "order by" clause, then the result will be the same no matter how many time you run the statement with the same group of data. The columns in the "order by" clause should identify uniquely each row in the set, if not you could get ties and the statement becomes un-deterministic.


    AMB

    Yeah, I agree with you on that
    Abdallah, PMP, MCTS
    Saturday, October 17, 2009 4:28 PM