SQLTeam.com | Weblogs | Forums

Find the records and delete it

Hi Peeps,
I am following condition of records in the table.
Spid Syncid
1234 1234
1234
1234

I want my sql select to only return row 2 and 3 only.

How can I achieve it?

Please advise.

If this get you the result you expect:

select *
  from yourtable as t
 where spid=1234
   and syncid is null
;

then this will delete them:

delete t
  from yourtable as t
 where spid=1234
   and syncid is null
;

Thanks but there are not just 3 rows there are more than 100k

select *
  from yourtable as t
 where syncid is null
;

I'm glad you understand what the O/P wants on such scant information ...

Sorry on some rows the spid and syncid would be matching don't need that to come out.

Do you have records where both spid and syncid are null?
If so and you want to keep them:

select *
  from yourtable as t
 where syncid is null
   and spid is not null
;