CODEWITHSUNDEEP
sqlsql-server-2008
The_DemoCorgin
The_DemoCorgin

Reputation: 744

Update a single column with data from another row in that column

I have a DB that has 4 columns, DOC_ID, DOC_CODE, ORIG_DOC_ID, and ITEM_ID. The DOC_CODE is either I or B, with each I DOC_ID having a corresponding B DOC_ID shown as ORIG_DOC_ID. Example:

-----------------------------------------------------------------
|    DOC_ID       |    ORIG_DOC_ID    | DOC_CODE  |    ITEM_ID  |
-----------------------------------------------------------------
|     33456       |    44567          |     I     |     NULL    |
|     44567       |    NULL           |     B     |     254     |
|     45785       |    67584          |     I     |     NULL    |
|     67584       |    NULL           |     B     |     234     |
-----------------------------------------------------------------

I want to run an UPDATE script that takes the ITEM_IDs from DOC_IDs with DOC_CODE B, and inserts them into the ITEM_ID Column in the corresponding DOC_ID with DOC_CODE I. So the table, after the script, would look like:

-----------------------------------------------------------------
|    DOC_ID       |    ORIG_DOC_ID    | DOC_CODE  |    ITEM_ID  |
-----------------------------------------------------------------
|     33456       |    44567          |     I     |     254     |
|     44567       |    NULL           |     B     |     254     |
|     45785       |    67584          |     I     |     234     |
|     67584       |    NULL           |     B     |     234     |
-----------------------------------------------------------------

Any advice would be much appreciated! Thanks!

Upvotes: 0

Views: 43

Answers (1)

Sparky
Sparky

Reputation: 15085

Try this:

update TableOne   set item_id = xx.Item_id
from       
(       
select doc_id,item_id
from TableOne
where Doc_Code='B'
) xx where TableOne.Orig_doc_id=xx.doc_id

SQL Fiddle: http://www.sqlfiddle.com/#!3/1a442/2

Upvotes: 1

Related Questions