Design a query to update values of a column basing on other value

Question

Design a query to update values of a column basing on other value

Annee

Ten Centuries

Points: 1064
More actions
March 27, 2012 at 9:51 am

#259201

Using SQL Server 2005
I have table which looks something like below:
(A given DocId can have one or more versions)
DocId Version Status
A1 0 HT
A1 1 DW
A1 2 DW
A2 0 DW
A2 1 DW
A3 0 HT
A4 0 DW
A4 1 HT
A4 2 HT
A4 3 DW
Now I added a new column to the table called OrigStatus
DocId Version Status OrigStatus
A1 0 HT NULL
A1 1 DW NULL
A1 2 DW NULL
A2 0 DW NULL
A2 1 DW NULL
A3 0 HT NULL
A4 0 DW NULL
A4 1 HT NULL
A4 2 HT NULL
A4 3 DW NULL
And I want to run a query which would update the value of OrigStatus for all the trans.
OrigStaus (of a given docId) = status (of the given docId where version = 0)
After running the query table looks like below:
DocId Version Status OrigStatus
A1 0 HT HT
A1 1 DW HT
A1 2 DW HT
A2 0 DW DW
A2 1 DW DW
A3 0 HT HT
A4 0 DW DW
A4 1 HT DW
A4 2 HT DW
A4 3 DW DW
Greatly apprecaite any help designing the query to update the value as mentioned above

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Sean Lange SSC Guru Points: 286590 More actions · Answer 1

If you can provide ddl (create table script) and sample data (insert statements) this is pretty simple. Take a look at the first link in my signature for best practices on posting questions.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Annee Ten Centuries Points: 1064 More actions · Answer 2

I have edited the the initial post for better readability and I am also posting the create and insert statements below:

CREATE TABLE [dbo].[DocumentHold](

[ReqID] [int] IDENTITY(1,1) NOT NULL,

[DocId] [nvarchar](50) NOT NULL,

[Version] [int] NULL,

[Status] [nvarchar](50) NULL,

CONSTRAINT [PK_DocumentHold] PRIMARY KEY CLUSTERED

(

[ReqID] ASC

))

INSERT INTO [DocumentHold]

([DocId],[Version],[Status])

SELECT 'A1',0,'HT', UNION ALL

SELECT 'A1',1,'DW', UNION ALL

SELECT 'A1',2,'DW', UNION ALL

SELECT 'A2',0,'DW', UNION ALL

SELECT 'A2',1,'DW', UNION ALL

SELECT 'A3',1,'HT', UNION ALL

SELECT 'A4',0,'HT', UNION ALL

SELECT 'A4',1,'HT', UNION ALL

SELECT 'A4',2,'HT', UNION ALL

SELECT 'A4',3,'HT', UNION ALL

After the new column is added, Create statement looks like below:

CREATE TABLE [dbo].[DocumentHold](

[ReqID] [int] IDENTITY(1,1) NOT NULL,

[DocId] [nvarchar](50) NOT NULL,

[Version] [int] NULL,

[Status] [nvarchar](50) NULL,

[OrigStatus] [nvarchar](50) NULL,

CONSTRAINT [PK_DocumentHold] PRIMARY KEY CLUSTERED

(

[ReqID] ASC

))

Sean Lange SSC Guru Points: 286590 More actions · Answer 3

Nice job with the sample data and ddl.

Something like this should do what you are looking for.

update dh

set OrigStatus = x.Status

from documenthold dh

cross apply

(

select top 1 Status

from DocumentHold d

where dh.DocId = d.docId

order by Version

)x

--then to validate the results

select * from DocumentHold

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Annee Ten Centuries Points: 1064 More actions · Answer 4

Annee

Ten Centuries

Points: 1064

March 27, 2012 at 1:16 pm

#1465297

Thanks! You are a Genius!!!

Sean Lange SSC Guru Points: 286590 More actions · Answer 5

Aww shucks. I certainly wouldn't say a genius but thanks for letting me know that worked for you. Make sure before you run that on your production box that you understand what it is doing. After all it will be you that has to support issues at 3am when phone rings. :hehe:

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/