identify data in the child table that dont match

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

Transact-SQL (2000)

identify data in the child table that dont match

Author

Topic

alejo46
Posting Yak Master

157 Posts

Posted - 2012-07-14 : 11:49:33

Good morning

Ive run a script to delete data from table called dim_aca_atencion and after 15 minutes running yields an error:
DELETE statement conflicted with COLUMN REFERENCE constraint 'FK_HECHOS_INCIDENCIAS_DIM_ACA_ATENCION'. The conflict occurred in database 'dm1_elite', table 'HECHOS_INCIDENCIAS', column 'COD_DIM_ATENCION'.

I've found the answer in this forum and because is a violation constrain from foreing key throuh colum DIM_ACA_ATENCION, the solution given is to delete data first from child table (hechos_incidencias table) right?

but this child table has no indexes and the cost is very expensive

My idea is to identify the rows that exist in the parent table but dont match in the child table in order to avoid the constrain
so, the below script is the right one ?

select hi.COD_DIM_ATENCION,daa.NUM_ATENCION,daa.FEC_ATENCION,daa.FEC_HISTORICO
from dim_aca_atencion daa
left outer join hechos_incidencias hi
on daa.cod_dim_atencion=hi.cod_dim_atencion
where daa.fec_historico >= '19990115' and daa.fec_historico <= '20021231'
and hi.cod_dim_atencion IS NULL

if so, based on the above script i could implemente the delete right ?

Id appreciate your help in advenced

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-07-14 : 13:19:16

That looks right. To delete you would simply replace the select with Delete as in:

DELETE daa
FROM   dim_aca_atencion daa
       LEFT OUTER JOIN hechos_incidencias hi
            ON  daa.cod_dim_atencion = hi.cod_dim_atencion
WHERE  daa.fec_historico >= '19990115'
       AND daa.fec_historico <= '20021231'
       AND hi.cod_dim_atencion IS NULL

If there are a large number of rows, you might try deleting a few at a time - for example by limiting the date range to a smaller interval. Alternatively, you could set a ROWCOUNT:

SET ROWCOUNT 100;
DELETE daa
FROM   dim_aca_atencion daa
       LEFT OUTER JOIN hechos_incidencias hi
            ON  daa.cod_dim_atencion = hi.cod_dim_atencion
WHERE  daa.fec_historico >= '19990115'
       AND daa.fec_historico <= '20021231'
       AND hi.cod_dim_atencion IS NULL

That will delete 100 rows. The SET ROWCOUNT is going to be removed in a future version of SQL Server, but since you are on SQL 2000, you should be fine.

alejo46
Posting Yak Master

157 Posts

Posted - 2012-07-16 : 19:13:29

thank you for your help, and it came up with a new problem,dont know if its a overhead problem because i run the script to delete at a few time (in batch) and after running this script for about 4 hours it didnt delete any row; as a second option i run the normal delete and after running a couple of hours it didnt delete either.
there are about 11 millions of rows to be deleted

the total amount for these 2 tables are:
DIM_ACA_ATENCION 705429697
HECHOS_INCIDENCIAS 142407076 - and the firt one has indexs, the second one has no index:IX_DIM_ACA_ATENCION_NUM_ATENCION_FUENTE nonclustered located on INDICES FUENTE, NUM_ATENCION
PK_DIM_ACA_ATENCION nonclustered, unique, primary key located on INDICES COD_DIM_ATENCION

and the column fec_historico is a datetime data type for the format YYYY-MM-DD-hh:mi:se.000

I appreciate your help in order to figure out this problem because the FG where data is stored run out of space

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2012-07-17 : 04:28:33

Get the PKs of the rows that need to be deleted into a table then join to that for the delete.
Use an identity on this table to batch the delete

Start with one row then 10 then 1000 ... until you find a good batch size.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

alejo46
Posting Yak Master

157 Posts

Posted - 2012-07-17 : 08:47:38

thanks a lot again for your help, just one more question, the down time for the delete could be also the comparison between dates (yyyymmdd) with the column fec_historico which is datetime format YYYY-MM-DD HH:MM:SS.000?

where daa.fec_historico >= '19990115'and daa.fec_historico <= '20021231'

jleitao
Posting Yak Master

100 Posts

Posted - 2012-07-17 : 09:38:03

yes, you can compare it.

See the example:

select
convert(datetime, '2012-07-17 00:00:00.000'),
convert(datetime, '20120712'),
case
when convert(datetime, '2012-07-17 00:00:00.000') = '20120717'
then 'Yes'
else 'No'
end

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-07-17 : 09:52:14

quote:
Originally posted by alejo46

thanks a lot again for your help, just one more question, the down time for the delete could be also the comparison between dates (yyyymmdd) with the column fec_historico which is datetime format YYYY-MM-DD HH:MM:SS.000?

where daa.fec_historico >= '19990115'and daa.fec_historico <= '20021231'

If the data type of the daa.fec_historico column is DATETIME, I would keep the WHERE clause exactly as you have it, with one change

where daa.fec_historico >= '19990115'and daa.fec_historico < '20030101'

I moved up the upper bounding date by one day, and used a strict less than condition. This would be better (for performance) than converting the daa.fec_historico column to a pure date format or a string format for doing the comparison.

alejo46
Posting Yak Master

157 Posts

Posted - 2012-07-18 : 17:29:20

Good afternoon

I've decided to use the PK (cod_dim_atencion) to run the query but before using it i needed
to find out the rage for the cod_dim_atencion number from date '19990115' and '20021231', firstable
i tried with just one day

select COD_DIM_ATENCION,FEC_HISTORICO
from dim_aca_atencion daa
where fec_historico >= '19990408' and fec_historico < '19990409'
order by COD_DIM_ATENCION

it yield me 526 rows affected and the minimum value for this column was 13 and the maximum value was 43361116

thats OK and i coded it with the delete was OK just one day , but when i moved up the lower and upper date '19990115' to '20021231' brought me just a few rows (10) like this

select COD_DIM_ATENCION,FEC_HISTORICO
from dim_aca_atencion daa
where fec_historico >= '19990115' and fec_historico < '20021231'
order by COD_DIM_ATENCION

Output

1 2002-06-05 00:00:00.000
3 1999-05-12 00:00:00.000
4 1999-06-02 00:00:00.000
5 1999-09-27 00:00:00.000
6 1999-09-27 00:00:00.000
7 1999-09-27 00:00:00.000
8 1999-04-14 00:00:00.000
9 1999-04-14 00:00:00.000
10 1999-04-23 00:00:00.000
11 1999-09-27 00:00:00.000

wHILE I RUN THIS QUERY IT YIELDS me 11.406.942 rows

SELECT count(*)
from DIM_ACA_ATENCION
WHERE FEC_HISTORICO >= '19990115'
AND FEC_HISTORICO <= '20021231'

in which query could i be wrong?

Thanks once again for your help

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-07-18 : 21:14:48

Two things:

a) I want to make sure that the data type of the FEC_HISTORICO column is DATETIME (or SMALLDATETIME). Otherwise, it may not work as expected.

b) For the two queries you posted (from 1999-01-05 to end of 2002) to be equivalent, you need to change the first query like this:

select COD_DIM_ATENCION,FEC_HISTORICO
from dim_aca_atencion daa
where fec_historico >= '19990115' and fec_historico <= '20021231'
order by COD_DIM_ATENCION

If you do, I would expect that the two queries would return the same number of rows.

alejo46
Posting Yak Master

157 Posts

Posted - 2012-07-18 : 22:47:44

OK, thank you

1.first FEC_HISTORICO is DATETIME and i confirmed it because mistakindly i typed a bad digit i.e instead of '1999115' i typed '19901115' and yield me and error: error converting datetime to varchar

2. im gonna try tomorrow morning your advise, but its not logic the resulsets for these queries turn inacurate and not what i expected.
thanks

alejo46
Posting Yak Master

157 Posts

Posted - 2012-07-19 : 17:28:50

Good afternoon, now i run this similar query:
select COD_DIM_ATENCION,FEC_HISTORICO from dim_aca_atencion daa
where fec_historico >= '19990115' and fec_historico < '20020301'
order by COD_DIM_ATENCION
it yielded me (2575316 row(s) affected)
but now im quite lost because i expected this query yields (11.406.942 rows) to match the SELECT count(*) query ?

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-07-19 : 21:11:19

I don't have any thoughts on why the numbers differ. If you run the two queries below, the number of rows returned by the first query should EXACTLY equal the RowCount returned by the second query.

The only exception I can think of is if the table is a live table and rows got added to or removed between the execution of the two statements.

-- 1
SELECT COD_DIM_ATENCION,
       FEC_HISTORICO
FROM   dim_aca_atencion daa
WHERE  fec_historico >= '19990115'
       AND fec_historico < '20020301'

-- 2      
SELECT COUNT(*) AS [RowCount]
FROM   dim_aca_atencion daa
WHERE  fec_historico >= '19990115'
       AND fec_historico < '20020301'

alejo46
Posting Yak Master

157 Posts

Posted - 2012-07-20 : 18:11:11

i think i found what the problem was, i only changed the upper and lower bounding day explicitely as a datetime, and reading some articles in this GREAT PAGE it has something to do the way how datetime data type is stored in sqlserver, so the query run OK and yields the expected number of rows in this way: (another choice is using the function FLOOR with combination with CAST function), so the query is:

SELECT COD_DIM_ATENCION,FEC_HISTORICO
FROM dim_aca_atencion
WHERE fec_historico >= '1999-01-15 00:00:00.000' AND fec_historico <= '2002-02-31 23:59:59.997'

anyway, thank you very much for your help

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-07-20 : 19:22:23

quote:
Originally posted by alejo46

i think i found what the problem was, i only changed the upper and lower bounding day explicitely as a datetime, and reading some articles in this GREAT PAGE it has something to do the way how datetime data type is stored in sqlserver, so the query run OK and yields the expected number of rows in this way: (another choice is using the function FLOOR with combination with CAST function), so the query is:

SELECT COD_DIM_ATENCION,FEC_HISTORICO
FROM dim_aca_atencion
WHERE fec_historico >= '1999-01-15 00:00:00.000' AND fec_historico <= '2002-02-31 23:59:59.997'

anyway, thank you very much for your help

I am happy for you that it worked, but I gotta tell you two things:

a) This means that the column fec_historico is not of type DATETIME or SMALLDATETIME even though in an earlier post you said it indeed is.

b) The link you mentioned in your post did not show, so I don't know if they are giving you bad advice, or you interpreted their advice incorrectly. In either case, what you are doing is not a safe thing to do. You are using a string that looks like a date, and can be interpreted as February 31, 2002 for the purpose of filtering on a date range. While that might appear to have solved your immediate problem, it surely can lead to incorrect results or failed queries at some point in the future.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources