CODEWITHSUNDEEP
sql
cindi
cindi

Reputation: 4801

Messy sql problem

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.

Upvotes: 0

Views: 229

Answers (3)

Robert Christie
Robert Christie

Reputation: 20695

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)

Upvotes: 2

Wim
Wim

Reputation: 11252

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)

Upvotes: 1

JonH
JonH

Reputation: 33163

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

Upvotes: 0

Related Questions