Hi,

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:

and it would be nice if it updates all Item_Bill entries at once.

Thanks,
Ole

What DBMS are you using? ANSI update behaviour/syntaxt differs from, say, Microsoft SQL Server, so yo may get a more appropriate answer if you supply us with this information

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.

Your example above is ANSI/ISO SQL compliant. That searched update syntax has been in the SQL standard for ages, at least since SQL-92, so I suppose most DBMS products support it.

Hmm, funny thing. I thought I had tried my statement some days ago and it had not worked, but another try today has proved me wrong.

Yes, we can dream, can't we.

You can use the MERGE statement to do an update between two tables based on a join. MERGE is ISO standard SQL and is supported by Oracle, MS SQL Server and DB2 among others.