Results 1 to 7 of 7
  1. #1
    Join Date
    Oct 2009
    Posts
    26

    Unanswered: Multi-Row-Update

    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:

    Code:
    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.

    Thanks,
    Ole

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    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
    George
    Home | Blog

  3. #3
    Join Date
    Oct 2009
    Posts
    26
    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.

  4. #4
    Join Date
    Dec 2008
    Location
    At work...
    Posts
    92
    Quote Originally Posted by olel View Post
    Nevertheless it would be great to have an statement which will work on any sql database [...] 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.

  5. #5
    Join Date
    Oct 2009
    Posts
    26
    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.

  6. #6
    Join Date
    Sep 2009
    Posts
    44
    Quote Originally Posted by olel View Post
    Nevertheless it would be great to have an statement which will work on any sql database...
    Yes, we can dream, can't we.

  7. #7
    Join Date
    Dec 2007
    Location
    London, UK
    Posts
    741
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •