I have a single table (derived from importing the content of 1000's of music files) in a non normalised database. The table contains the attributes of multiple albums, incl:
- __dirpath (path from which data was pulled ... unique to each album)
- foldername (folder in which tracks representing album resides)
- discnumber (can be blank or numeric, representing discnumber from which track is taken)
- track (numeric representing track number in album, sequential from 1..n where n is number of tracks)
- album (name of album)
For every track making up an album there is an entry in the table. The metadata from which the table was created is inconsistent in that:
- some albums that are single disc albums have a discnumber of "1" only, others that are single disc albums may be blank
I'd like to run an update query on the table to delete all instances of discnumber where for a given dirpath all tracks have a discnumber set to "1"
This code shows all instances where __dirpath has tracks with a discnumber entry. If I can then narrow that result down to those where __dirpath only has a discnumber of '1' I can run an update statement to remove the discnumber from all records with a matching __dirpath. But I'm stuck as I don't regularly work in SQL.
SELECT DISTINCT __dirpath,
WHERE discnumber IS NOT NULL
ORDER BY __dirpath,
Any ideas how to go about this?