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

Answer 1

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

Answer 2

BEGIN TRAN

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

COMMIT TRAN

---

Abdallah, PMP, MCTS

Answer 3

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

Answer 4

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

Answer 5

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

Answer 6

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

Answer 7

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

Answer 8

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

Answer 9

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

Answer 10

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

Answer 11

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

Answer 12

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

Answer 13

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

Answer 14

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

Answer 15

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

Answer 16

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

---

Abdallah, PMP, MCTS

Answer 17

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

Answer 18

;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

Answer 19

;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

Answer 20

;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

Answer 21

;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