-1

I have a table (MySQL) like this:

ID    |   AnotherID  |   Number1      |      Number2   |    Data   
1           A             101                 199           Lorem  
2           A             102                 200           
3           A             201                 299                           
4           A             202                 300           Impsun  
5           B             1001                1099  
...

Basically, I need to compare in sets of 2 rows and make a SQL Query to achieve this logic:

IF row1.anotherID = row2.AnotherID AND
row2.Number1 + row2.Number2 - row1.Number1 - row1.Number2 = 2 AND
row2.ID - row1.ID = 1

THEN

IF some of this 2 rows have the Data field populated and the other row don't 
THEN Copy the Data field into the empty one
ELSE do nothing

Final table should be like this

ID    |   AnotherID  |   number1      |      number2   |    data   
1           A             101                 199           Lorem  
2           A             102                 200           Lorem
3           A             201                 299           Impsun                
4           A             202                 300           Impsun  
5           B             1001                1099  

...

How can it be done? Thanks!

1

I think you want something like this:

update t join
       t t2
       on t.AnotherId = t2.AnotherId and
          t.Number1 + t.Number2 - t2.Number1 - t2.Number2 in (-2, 2) and
          t.id - t2.id in (1, -1)
    set t.data = t2.data
    where t.data is null and t2.data is not null;

The idea is to pair each row in t with the matching row in t2 (if any). Note that the matching row could be above or below, by the logic you give. That is why the last two conditions are bi-directional.

| improve this answer | |
  • Hi, thanks for the answer. What should be t2? Could you give me the exact query if the example table name is "Tbl"? Thanks – Richmond Dec 20 '13 at 0:48
  • t2 is a table alias. The query would start update tbl t join tbl t2 . . . . – Gordon Linoff Dec 20 '13 at 0:56
  • Thanks for the reply, but the above code give me "0 rows affected" ... – Richmond Jan 2 '14 at 13:28
  • It works!!! had to replace IS NULL to = '' and IS NOT NULL to != '' – Richmond Jan 2 '14 at 13:36
0

Try this:

select
  ID,
  AnotherID,
  Number1,
  Number2,
  (
    select t2.Data
    from Test t2
    where t2.Data is not null and t2.ID <= t1.ID
    order by t2.ID desc
    limit 1
  ) as SelectedData
from Test t1
order by ID

I created a SQL Fiddle example.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.