Mysql
使用 3 個表更新、選擇和加入
有3張表:
Label (Label_ID,Label_Name)
, Label_ID 是主鍵。Tracks (Prefix, Label_ID)
, Prefix 為 Primary Key,Label_ID 為空列Top100 (Prefix,Label)
此表中沒有鍵。我的查詢應該是這樣的,對於 Tracks 表中的每一行
prefix
,在top100表中找到一個等效項,然後從top100表中選擇,匹配因此在Label表中選擇,獲取它並在Tracks表中更新它的值。prefix``Label``Label``Label_name``Label_ID
誰能幫我解決這個問題??非常感謝任何幫助
我猜您不希望(對於每個
Tracks.prefix
)插入新行,但要更新現有行(否則您會在 的主鍵上發生衝突Tracks
)。您可以通過此查詢首先檢查將要更改的內容:SELECT tr.prefix, tr.label_id AS existing_label_id, top.label, lbl.label_id AS new_label_id FROM Tracks AS tr JOIN Top100 AS top ON top.prefix = tr.prefix JOIN Label AS lbl ON lbl.label_name = top.label ;
然後更新:
UPDATE Tracks AS tr JOIN Top100 AS top ON top.prefix = tr.prefix JOIN Label AS lbl ON lbl.label_name = top.label SET tr.label_id = lbl.label_id ;