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.
Author |
Topic |
Flaterik
Starting Member
26 Posts |
Posted - 2012-09-25 : 09:58:55
|
Hi people i have a table like thisCOD TYPE VALUE DEFAULTC001 WWW XXXX 0 C001 EMAIL YYY 0C001 EMAIL ZZZ 0C001 CEL NNN 0C002 WWW XXXX 0etc etcSo i must update the filed 0 with 1But because of a key value, i can update from 0 to 1 the same field of the same customerSo i mus put 1 only on C001 EMAIL YYY , not in C001 EMAIL ZZZ.HOw i can remove the duplicated type grouped by COD? I can order this with another field************************************************the world is strange but people are crazy |
|
Ifor
Aged Yak Warrior
700 Posts |
Posted - 2012-09-25 : 10:23:37
|
[code]-- *** Test Data ***CREATE TABLE #t( COD char(4) NOT NULL ,[Type] varchar(5) NOT NULL ,Value varchar(5) NOT NULL ,[Default] int NOT NULL);INSERT INTO #tSELECT 'C001', 'WWW', 'XXXX', 0 UNION ALL SELECT 'C001', 'EMAIL', 'YYY', 0UNION ALL SELECT 'C001', 'EMAIL', 'ZZZ', 0UNION ALL SELECT 'C001', 'CEL', 'NNN', 0UNION ALL SELECT 'C002', 'WWW', 'XXXX', 0;-- *** End Test Data ***select * from #t;WITH CODTypeOrderAS( SELECT * ,ROW_NUMBER() OVER (PARTITION BY COD, [Type] ORDER BY Value) rn FROM #t)UPDATE CODTypeOrderSET [Default] = 1WHERE rn = 1;select * from #t;[/code] |
|
|
Flaterik
Starting Member
26 Posts |
Posted - 2012-09-25 : 11:17:21
|
Hi, thank you very much for the response.It's right what you say to me but ther's another problemI must update a table of my db called PeopleAddressMedia.How i can do this? In your solution you create a CTE and update thisI must update the other tableThanks************************************************the world is strange but people are crazy |
|
|
Ifor
Aged Yak Warrior
700 Posts |
Posted - 2012-09-25 : 11:52:53
|
[code]WITH CODTypeOrderAS( SELECT * ,ROW_NUMBER() OVER (PARTITION BY COD, [Type] ORDER BY Value) rn FROM PeopleAddressMedia)UPDATE CODTypeOrderSET [Default] = 1WHERE rn = 1;[/code] |
|
|
|
|
|
|
|