I need some help with a somehow weird update statement. I have three tables Item, Bill and Item_Bill. Item and Bill have a n:m relationship in our domain model so that we have the connection table Item_Bill which has three attributes: item_id, bill_id and some_id (see below).
In the table Item we have an attribute "some_id" that is mandatory and whose function isn't relevant for this problem. In the table Item_Bill we also have this attribute "some_id" but it is optional here and actually always empty.
Is it possible to write a sql update statement to fill the "some_id" attributes in all Item_Bill entries with the corresponding "some_id" from Item? I should look like:
update Item_Bill set some_id = (select some_id from Item where Item.id = Item_Bill.item_id)
and it would be nice if it updates all Item_Bill entries at once.
Our customer is using DB2. Nevertheless it would be great to have an statement which will work on any sql database as we are using Hibernate as our ORM and would like to be independent from our customers DBMS.