help on query

[inexion]

hello,

can't figure out this query, any help 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!!

[jero]

You may try something in this direction :

INSERT INTO table2
SELECT * FROM table1
WHERE value3 < 1.0
GROUP BY nid
HAVING count(*) > 1
UNION
SELECT * FROM table1
GROUP BY nid
HAVING count(*) = 1

jero