Need help with UPDATE

[spartan2010]

Hello Friends,

I have a table with Surrogate key,4 additional key columns & 2 non key columns.
Here are three sample rows.

Surr keys non-keys

777 1 2 3 4 Y 777
888 1 2 3 4 Y 888
999 1 2 3 4 N 999

I need to take the value 999 & replace 777 & 888s in the non-key last column.In otherwords 999 is the old surrogate key (I have the same value in a non-key column also)& I need to update the new rows' non-key column value with the old surrogate key value.

I need the following result.

Surr keys non-keys

777 1 2 3 4 Y 999
888 1 2 3 4 Y 999
999 1 2 3 4 N 999



How can I easily accomplish that?

 

[bborissov]

UPDATE YourTable SET NonKeys = Tbl1.NonKeys
FROM YourTable
INNER JOIN (SELECT NonKeys 
                   FROM YourTable
            WHERE Surr = 999) Tbl1 ON 1 = 1

Borislav Borissov
VFP9 SP2, SQL Server 2000,2005 & 2008.

 

[spartan2010]

Thanks Boris for the swift response..

The problem is slightly more complex..

777 1 2 3 4 Y 777
888 1 2 3 4 Y 888
999 1 2 3 4 N 999

444 2 2 3 4 Y 444
555 2 2 3 4 Y 555
666 2 2 3 4 N 666

It is a fact table with full of values & 999 is a running no..1,2,3....Not just 999 always...
When I use the above update it updates all the rows in the table with the same 999 value...

It should populate 666 for the second set of rows..and so on
based on the surrogate key value for the given key combination..

Can I email you my query offline Boris?

Thanks agian for helping me out....

 

[bborissov]

Post it here.
There are many great members that could give you more elegant and easy way to do what you want.


Borislav Borissov
VFP9 SP2, SQL Server 2000,2005 & 2008.

 

[markros]

It looks to me you want to update your values with the max value per set using the Keys field.

PluralSight Learning Library

 

[spartan2010]

Hi Markos,

For all the key combinations,I need to get the Surrogate key value where the flag = N & update the Type2surrkey column for the rows of the same key combination & the non-key column = Y.In otherwords all the records with the same key combination should have the same Type2surrkey column value & it is the value from the record where flag=N.(999 or 666)

source
Surr key col flag Type2surrkey

777 1 2 3 4 Y 777
888 1 2 3 4 Y 888
999 1 2 3 4 N 999

444 2 2 3 4 Y 444
555 2 2 3 4 Y 555
666 2 2 3 4 N 666

Result after the update

777 1 2 3 4 Y 999
888 1 2 3 4 Y 999
999 1 2 3 4 N 999

444 2 2 3 4 Y 666 <---New key gets replaced with old key
555 2 2 3 4 Y 666 <---New key gets replaced with old key
666 2 2 3 4 N 666 <---Original record's key (old)

 

[markros]

update Source set surrKey = S1.SurrKey 
from Source inner join 
(select Key, Col, Flag, max(SurrKey) 
from Source where Type2='N' group by Key, Col, Flag) S1 
on Source.Key = S1.Key
and Source.Col = S1.Col 
and Source.Flag = S1.Flag where Source.Type2 = 'Y'

PluralSight Learning Library

 

[spartan2010]

Thanks a lot Markros..I did a few modifications to your UPDATE statement..It works like a charm..Thank You...

I really appreciate it...Thanks to Boris too..U guys are awesome...