Flagging first occurance - 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

SQL Server 2000 Forums

SQL Server Development (2000)

Flagging first occurance

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2002-04-15 : 08:43:38

Larry writes "Sorry if it is here but after 4 SQL books and browsing the net for a week I'm just not seeing it.

I have a large database that I import from time to time. I need to do a quick cleanup to flag the first occurance of an item string. Distinct and other methods don't seem to allow me to see or update a seperate field.

Only three fields/columns are necessary to explain the problem. I have one database I'm dealing with. The three fields consist of:
Field 1: a string where there are duplicates
Field 2: a numeric (integer) value
Field 3: a boolean field use to flag a record

I want to flag (set to true) the boolean field that corresponds to the first occurance of each duplicate in the string field.

Can anyone show me an example?
LG"

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2002-04-15 : 09:23:24

I am gonna assume you have...


create table larry (
    dupme    varchar(10),
    someint  integer,
    flag     bit )
go

insert larry values ('Page47',1,0)
insert larry values ('Page47',2,0)
insert larry values ('Page47',3,0)
insert larry values ('Larry',1,0)
insert larry values ('Larry',2,0)
insert larry values ('Larry',3,0)
go

...Now, I am making a big assumption here, and that is that dupme + someint is at least a candidate key. That aside, you can do a little dance like...


update larry
set flag = 1
from larry l
where 
    exists (
        select 1
        from
            larry
        where
            dupme = l.dupme and
            someint = l.someint
        group by
            dupme
        having (select
                    count(*)
                from
                    larry
                where
                    dupme = l.dupme and
                    someint < l.someint ) < 1 )
go
select dupme,someint,flag from larry
go

...For future reference, if you give us ddl (create table statements) and sample data, we can all be on the same page ....

<O>

gulfsailor
Starting Member

1 Post

Posted - 2002-04-15 : 17:13:45

Great. I ended up writing a C++ program this weekend but I'll study this. Should answer many questions I've had over time.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources