update troubles - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

update troubles

Author

Topic

WJHamel
Aged Yak Warrior

651 Posts

Posted - 2011-11-28 : 17:59:38

I have a table to which i've imported some data into temporary columns therein with the intent of using those temp columns to update permenant columns. The problem is this. My permenant columns in this table (EVP_PER) are:
Uniquekey - populated and all is good;
EVPNO - populated and all is good;
PERSONTYPE - needs data from the temp columns;
NAME - Needs data from the temp columns.

the temp columns i've imported are:
VICTIM;
SUSPECT.

The names held in these columns dictate the identity of a person (NAME) and their status as a victim (Value 'V' in persontype) or a suspect (value 's' in persontype).

In some rows, i only have a victim, in other rows i only have a suspect. In still others, i have both. The problem lies in the cases where both the VICTIM column and the SUSPECT column are both populated for the same row. In those cases, i would need an additional row created with a duplicate EVPNO number to accomodate two entries in the persontype field (S, and V) for the same EVPNO entry. What do i need to add to an update query to create new rows with the same EVPNO number when i have both suspects and victims for the same EVPNO entry?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-29 : 03:37:26

[code]
update t
set t.PERSONTYPE = 'S',
t.NAME=SUSPECT
from table t
where SUSPECT is not null
and PERSONTYPE is null

update t
set t.PERSONTYPE = 'v',
t.NAME = VICTIM
from table t
where VICTIM is not null
and PERSONTYPE is null
and SUSPECT is null

insert table
select EVPNO,'V',VICTIM
from table
where VICTIM is not null
and SUSPECT is not null
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

WJHamel
Aged Yak Warrior

651 Posts

Posted - 2011-11-29 : 15:18:29

Once again, you've led me down the right path. Thank you again.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-30 : 01:38:51

wc

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources