Update one row of a row pair, where both rows toge

问题:

How do I obtain both ids of a pair of rows with certain specs?

More specific:

id - spec1 - spec2 - spec 3
1    null    30 nov  34
2    null    30 nov  34
3    null     1 dec  35
4    75       1 dec  35

I want to update spec3 of the rows that have the same date and where none of the rows has a value for spec1.

In the case of this example: I want to update row 1 and 2 -> same date, no values for spec1

I don't want to update row 3 and 4 -> same date BUT row 4 has a value for spec1

回答1:

you can get the id(s) from this query

select a.id id from tbl a, tbl b where a.spec2 = b.spec2 and isnull(a.spec1) and isnull(b.spec1) and a.id != b.id;

回答2:

UPDATE my_table t1
  JOIN my_table t2
    ON t1.spec1 IS NULL
   AND t2.spec1 IS NULL
   AND t1.spec2 = t2.spec2
   AND t1.id    < t2.id
SET    t1.spec3 = 123,
       t2.spec3 = 456

See it on sqlfiddle.