Messy sql problem

Question

Table A stores data in rows like

id, value1, value2

where id is always an even number. Table B stores this data in the form

id,   value1
id-1, value2

or else

id,   value1+value2

I need to do a once-off update of table B with the values taken from table A. Are there any elegant ways of doing this?

---

Clarification :

To answer Nate C-K's question below (which gets to the heart of the matter), the only way of knowing which arrangement applies to a record from table A is by inspecting the values(of id) in table B. This is why the problem is messy.

Answer 1

Assuming the logic you want is the following

• Where id and id - 1 are both in tableb, update both rows
• where no id - 1 row exists, update id with value1 + value 2

Then this should work

update b
set b.value = a.value1
from tableb b join tablea a on a.id = b.id
where 0 = a.id % 2
and a.id - 1 in (select id from tableb)

update b
set b.value = a.value2
from tableb b join tablea a on a.id = b.id - 1
where 0 = a.id % 2
and a.id - 1 in (select id from tableb)

update b
set b.value = a.value1 + a.value2
from tableb b join table a on a.id = b.id
and a.id - 1 not in (select id from tableb)

Answer 2

Update b SET b.value1 = a.value1 FROM
TableB b INNER JOIN TableA a ON a.id = b.id

Answer 3

Set B to (id, value1), (id+1, value2) -- latter one only when id+1 exists in B

update b
  set b.value = a.value1
  from tableb b join table a on a.id = b.id
  where 0 = a.id % 2

update b
  set b.value = a.value2
  from tableb b join table a on a.id = b.id - 1
  where 0 = a.id % 2

Now, for id's where id+1 is not in B, add value2 to the id one.

update b
  set b.value += (select a.value2 from a where a.id = b.id)
  where b.id - 1 not in (select id from b)