Help with update statement

Question

Post reply

Help with update statement

dt293

Default port

Points: 1412
More actions
November 4, 2008 at 4:56 am

#195592

Hi all,

I have a table I need to update based on certain criteria. Please see example data below;

RegNo Surname FirstName DateofBirth SNo CCode Pt1 Ct1

1605308 Bloggs Joe 01/05/1991 C0286371 1210 F1 FT

1724358 Simpson Homer 01/06/1978 C0293559 1905 F1 PT

1724358 Simpson Homer 01/06/1978 C0293559 1905 F1 RV

1686554 Smith Fred 01/06/1979 C0134727 1910 F1 PT

I need to check to see if SNo, CCode and Pt1 occur more than once, have the same values, then check to see if the value for Ct1 is different. If true then update Ct1 to the value 'PT'.

From the example above we can see that Homer Simpson matches the criteria needed to be updated as his SNo, CCode and Pt1 values occur more than once but his Ct1 value is different.

Any help would be much appreciated.

Thanks 🙂

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Garadin One Orange Chip Points: 29613 More actions · Answer 1

Please refer to the article in my signature for the proper way to post table DDL and sample data. Doing so will get you better, quicker answers.

Seth Phelabaum

Consistency is only a virtue if you're not a screwup. 😉

Links: How to Post Sample Data[/url] :: Running Totals[/url] :: Tally Table[/url] :: Cross Tabs/Pivots[/url] :: String Concatenation[/url]

Mark Cowne One Orange Chip Points: 26865 More actions · Answer 2

Something like this?

update mytable



set Ct1='PT'

where Ct1<>'PT'

and exists (

select t.SNo,t.CCode,t.Pt1

from mytable t

where t.SNo=mytable.SNo

  and t.CCode=mytable.CCode

  and t.Pt1=mytable.Pt1

group by t.SNo,t.CCode,t.Pt1

having count(*)>1 and count(distinct t.Ct1)>1

)

____________________________________________________

Deja View - The strange feeling that somewhere, sometime you've optimised this query before

How to get the best help on a forum

http://www.sqlservercentral.com/articles/Best+Practices/61537

dt293 Default port Points: 1412 More actions · Answer 3

IF OBJECT_ID('TempDB..#1','U') IS NOT NULL DROP TABLE #1

CREATE TABLE #1

(

[RegNo] varchar(30),

[Surname] varchar(50),

[FirstName] varchar(50),

[DateofBirth] datetime,

[SNo] varchar(20),

[CCode] varchar(20),

[Pt1] varchar(30),

[Ct1] varchar(30)

)

INSERT INTO #1

([RegNo],[Surname],[FirstName],[DateofBirth],[SNo],[CCode],[Pt1],[Ct1])

SELECT '1605308', 'Bloggs', 'Joe', '01/05/1991', 'C0286371', '1210', 'F1', 'FT' UNION ALL

SELECT '1724358', 'Simpson', 'Homer', '01/06/1978', 'C0293559', '1905', 'F1', 'PT' UNION ALL

SELECT '1724358', 'Simpson', 'Homer', '01/06/1978', 'C0293559', '1905', 'F1', 'RV' UNION ALL

SELECT '1686554', 'Smith', 'Fred', '01/06/1979', 'C0134727', '1910', 'F1', 'PT'

SELECT * FROM #1

Apologies for my lack of forum etiquette 😉

dt293 Default port Points: 1412 More actions · Answer 4

dt293

Default port

Points: 1412

November 4, 2008 at 7:15 am

#893602

Perfect. Thank you very much Mark.

j nuñez SSCrazy Points: 2115 More actions · Answer 5

j nuñez

SSCrazy

Points: 2115

November 4, 2008 at 7:54 am

#893633

And, there is another (easier) way to do the same

UPDATE #1

SET #1.Ct1 = 'PT'

FROM #1 INNER JOIN #1 as T2

ON #1.SNo = T2.SNo

AND #1.CCode = T2.CCode

AND #1.Pt1 = T2.Pt1

WHERE #1.Ct1 <> 'PT'