CODEWITHSUNDEEP
sqlsql-servert-sql
Eric
Eric

Reputation: 705

Error converting data type varchar to bigint

I have a table with data and one of the columns contains a number stored as text. When an application updates it, it writes _BAK + datetime stamp behind the number.

I'm now trying to clean up the database by deleting all records that have _BAK in the number column where the most recent one must not be deleted.

id     sitenummer                  
28376  1441_BAK20130213151952032    
28377  1441_BAK20130214142314705

In this case the line with ID 28376 is the oldest and must be removed.

I have created a query that should do just that:

;with sel1 AS (
select t1.ID,t1.sitenummer, CONVERT(BIGint,SUBSTRING(t1.sitenummer,CHARINDEX('_',t1.sitenummer,0)+4,50)) as Stamp1    
from vdfkraan as t1
where t1.sitenummer like '%_BAK%' and (SELECT COUNT(SUBSTRING(t2.sitenummer,0,CHARINDEX('_',t2.sitenummer,0))) FROM vdfkraan as t2 
                                      where SUBSTRING(t1.sitenummer,0,CHARINDEX('_',t1.sitenummer,0))=SUBSTRING(t2.sitenummer,0,CHARINDEX('_',t2.sitenummer,0))) > 1
group by t1.id,t1.sitenummer)

, sel2 AS (
select t3.id, t3.sitenummer, t3.stamp1, 
      (select TOP(1) t4.stamp1 from sel1 as t4
        WHERE SUBSTRING(t4.sitenummer,0,CHARINDEX('_',t4.sitenummer,0)) =SUBSTRING(t3.sitenummer,0,CHARINDEX('_',t3.sitenummer,0))
        order by t3.Stamp1 DESC) AS stamp2 from sel1 as t3)

, sel3 AS (select id from sel2 where Stamp1=stamp2)

--delete FROM vdfkraan
--where id IN (SELECT t1.id FROM sel3 as t1)

--select * from sel2

If I uncomment the last line (select * from sel2), it produces the following table:

id     sitenummer                 stamp1             stamp2
28376  1441_BAK20130213151952032  20130213151952032  20130213151952032
28377  1441_BAK20130214142314705  20130214142314705  20130213151952032

Table sel3 contains one record with one column id = 28376.

So that seems to work just as I want it.

Now I comment the select line and uncomment the Delete lines.

Now I get the following error:

Msg 8114, Level 16, State 5, Line 2
Error converting data type varchar to bigint.

So without the delete lines, all is ok, no errors, but with it, I get this error. I have checked the data, there should not be any problem.

What is going on here?

Upvotes: 2

Views: 5486

Answers (2)

msi77
msi77

Reputation: 1632

I think you can use grouping instead of window function:

SELECT  max(id), max(sitenummer)
FROM    vdfkraan
group by left(sitenummer,charindex('_BAK',sitenummer));

Upvotes: 0

Ivan Golović
Ivan Golović

Reputation: 8832

Try with this:

SELECT  v.ID
        , v.sitenummer
        , ROW_NUMBER() OVER (PARTITION BY LEFT(v.sitenummer, PATINDEX('%_BAK%', v.sitenummer) - 1) ORDER BY v.id DESC) num
INTO    #temp
FROM    vdfkraan v
WHERE   PATINDEX('%_BAK%', v.sitenummer) > 0

DELETE  vdfkraan
FROM    #temp t
JOIN    vdfkraan v ON v.id = t.id
AND     t.num <> 1

SELECT  *
FROM    vdfkraan

Here is an SQL Fiddle

Upvotes: 2

Related Questions