SitePoint Sponsor

User Tag List

Results 1 to 3 of 3
  1. #1
    SitePoint Enthusiast
    Join Date
    Feb 2005
    Location
    Glasgow, Scotland
    Posts
    97
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    How to update the connecting table in a one-to-many relationship

    So I have three tables

    Code:
    distro_items
    itemid, etc.
    
    distro_items_artist
    itemid, artistid
    
    distro_artist
    artistid, etc.
    I want to edit items, and allow for changing the artist on an item (not that this could ever happen, but for completeness). They cannot add or remove artists, just change the ones that are already there... so I need to get the matches for an itemid in distro_items_artist and change the artistid value to the new values. But whenever I run the update command it only ever updates the first row. Is there a way round this problem?

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,220
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    if the only column(s) in a table is/are the a primary key (even if it's a composite primary key, as is the case in this many-to-many relationship table), you should probably be doing inserts and deletes only, not updates

    if you have item 42 with artist 501 and also item 42 with artist 523, and this second one is wrong and should be artist 541 instead, then you could update that row --

    update distro_items_artist set artistid=541
    where itemid=42 and artistid=523

    or you could do this --

    delete from distro_items_artist where itemid=42 and artistid=523;
    insert into distro_items_artist values (42, 541)
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Enthusiast
    Join Date
    Feb 2005
    Location
    Glasgow, Scotland
    Posts
    97
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Yeah, I thought of the DELETE then INSERT again. However, I thought that would be much more inefficient and was wondering if there was a standard way to do this. Doing it that way would be make it much easier to add or remove artists too, so I'll just do that.

    Thanks!


Bookmarks

Posting Permissions

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