SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2012 Forums
 Transact-SQL (2012)
 Insert help
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

maddyslayer
Yak Posting Veteran

57 Posts

Posted - 03/13/2013 :  14:57:24  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

Germany
8679 Posts

Posted - 03/13/2013 :  15:09:36  Show Profile  Visit webfred's Homepage  Reply with Quote
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 - 03/13/2013 :  15:13:14  Show Profile  Reply with Quote
you can just use list_id=list_id and code_state='Y'
Go to Top of Page

webfred
Flowing Fount of Yak Knowledge

Germany
8679 Posts

Posted - 03/13/2013 :  15:32:06  Show Profile  Visit webfred's Homepage  Reply with Quote
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



Too old to Rock'n'Roll too young to die.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000