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
 SQL Server 2012 Forums
 Transact-SQL (2012)
 Insert help

Author  Topic 

maddyslayer
Yak Posting Veteran

57 Posts

Posted - 2013-03-13 : 14:57:24
How do insert a new record with cus_code = 'A' for each customer? Duplicates are not allowed.

list_id cus_id cus_code code_state

1 20 x Y
2 20 y y
3 20 z y
4 20 p N
5 21 x N
2 21 y y
3 21 z y
4 21 p N

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2013-03-13 : 15:09:36
what are the needed values for list_id and code_state then?


Too old to Rock'n'Roll too young to die.
Go to Top of Page

maddyslayer
Yak Posting Veteran

57 Posts

Posted - 2013-03-13 : 15:13:14
you can just use list_id=list_id and code_state='Y'
Go to Top of Page

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2013-03-13 : 15:32:06
[code]declare @sample table (list_id int, cus_id int, cus_code varchar(30), code_state varchar(30))
insert @sample
select 1,20,'x','y' union all
select 2,20,'y','y' union all
select 3,20,'e','y' union all
select 1,21,'z','y' union all
select 5,21,'A','y'

select * from @sample

insert @sample(list_id, cus_id, cus_code, code_state)
select
max(list_id),
cus_id,
'A',
'Y'
from @sample t1
where not exists(select * from @sample t2 where t2.cus_code='A' and t2.cus_id = t1.cus_id)
group by cus_id

select * from @sample
order by cus_id[/code]


Too old to Rock'n'Roll too young to die.
Go to Top of Page
   

- Advertisement -