script/query help

inexion

hello,

can't figure out this query, i don't think this can be done all in mySQL, but any help much appreciated

i need to order all duplicates by their unique id (nid)......then take those duplicates and reduce them to only one record per nid value. the remaining values are then checked to see if a particular column (bv) is less than a value. these remaining values are then used to mark a column in a separate table (dltme) to 1

example:

table1 (has duplicates):
id nid value1 value2 value3 dltme
1 01-1 0.32 0.45 0.11 0
2 01-3 0.12 0.75 0.31 0
3 01-4 0.37 0.25 0.17 0
4 01-1 0.32 0.45 0.11 0
5 01-1 0.32 0.45 0.11 0
(duplicate id's : 1,4)

step #1
=> (duplicates)
1 01-1 0.32 0.45 0.11 0
4 01-1 0.32 0.45 0.11 0
5 01-1 0.32 0.45 0.11 0

step #2
=> (reduce)
1 01-1 0.32 0.45 0.11 0

step #3
=> (check, value3 < 1.0)
1 01-1 0.32 0.45 0.11 0

step #4
=> (mark dltme on whichever id# has nid=01-1 in TABLE2 to 1)

table2 (has no duplicates):
id nid value1 value2 value3 dltme
1 01-1 0.32 0.45 0.11 1
2 01-3 0.12 0.75 0.31 0
3 01-4 0.37 0.25 0.17 0

if you need more explanation ill try - i just can't get this to work.....thanks!!

laserlight

You may be able to use an SQL statement along the lines of:

UPDATE table2 SET dltime=1
WHERE nid IN (SELECT nid FROM table1 WHERE value3<1.0 GROUP BY nid HAVING COUNT(nid)>1);

inexion

thanks laserlight -

so a couple things, the inner select is taking all the rows by nid in the duplicates table and sorting them by value3<1.0, and then grouping those results by nid for anything having a count > 1. so, does going between two tables that are both indexed take some time i suppose? that query took about 5500 seconds for about 20,000 records, but i guess after a while with so many records its going to take some time.

the other thing thats strange, the inner select should be selecting all the duplicates but only taking the number of unique nid's, so why would the results of your inner select and the following select differ(?):

SELECT nid, count( * ) AS cnt
FROM table1
GROUP BY nid
HAVING cnt >1;

the results were off by about a thousand records, i merged both and looked at it, but it didn't tell me anything useful. i guess im just curious without having to run another query that takes an hour and a half in the background =]

i will do some more testing, thanks laser!!

laserlight

hmm... 20K rows is not many, actually, so it probably means that the SQL statement that I suggested is terribly inefficient when compiled for MySQL, or do you not have the indices in the right places (or you simply are testing on a very, very slow computer, but that's unlikely).

Incidentally, how did you end up with table1 in the first place? It may be simpler to just insert or update table2 directly.

inexion

ok lets see, maybe this would be easier....

say you have two tables.....

table2 is all the originals, no duplicates and a unique field other than the id......
table1 is all the processed data, with duplicates
(both tables have all the same fields)

so, the reason i create table1 in the first place, is to generate some data that gets attached to further specify what kind of stars these are (spectral type, and absolute magnitudes) - basically all the stars from table2 are checked against a bank of other data and it joins those two fields from matching values.

example:

table2
id nid value1 value2 value3
1 01-1 0.00 0.31 0.11

spectral table
spectype absmag table2.value1+table2.value2 table2.value3+table2.value1
F5 0.55 0.01 0.31 0.62

table1
id nid value1 value2 value3 spectype absmag
1 01-1 0.00 0.31 0.11 F5 0.55

so the data i want, which is that star 01-1 is spectype F5 with absmag of 0.55 since table2.value1+table2.value2 = 0.31......so this is why table1 has so many duplicates, because different values will align with different spectral types and return more than one result per star since each star has 6 different values to check against 6 other tables full of types, and magnitudes etc....

so the question i guess is......can i just take the spectral types and magnitudes assigned in table1 and join those 2 columns to table2 - BUT only one of them per unique key (nid) ??

that would be the most ideal, hopefully this is a decent explanation - thanks again!

inexion

ok, maybe this would be easier -

how could you exclude records when merging tables? for example....

create table select * from targets where nid != ( select nid from goodtargets );
( i know this code won't work, but something that does this exactly)

basically make a new table that takes all the original values except the ones inside 'goodtargets'

laserlight

create table select * from targets where nid != ( select nid from goodtargets );
( i know this code won't work, but something that does this exactly)

That would be accomplished with NOT IN instead of !=.