[TIP] Combining columns in sql

[noisyscott]

I had mistakenly used one of my custom fields for half of my collection and the built in CollectionID field for the other half and needed to combine the two to set things right. After some searching and experimenting, I finally built the following SQL query to combine the two columns of data to an empty column from where I could copy back over to the desired location.

UPDATE ZENTRY
SET ZCUSTOM9 = ( SELECT CASE WHEN ZCOLLECTIONID IS NULL THEN ZCUSTOM2 WHEN ZCOLLECTIONID = '' THEN ZCUSTOM2 ELSE ZCOLLECTIONID END AS ZCUSTOM9 )

Thanks so much to drjohncmac for supplying the info to use the SQLite Database Browser which helped quite a bit.

Thanks again for DVDPedia. It is a dream!

other key references were this post and the inital thread at Bruji.com that described the SQLite usage.

[Conor]

Thank you for the tip. One can also merge one column with another with:

Code: Select all

update zEntry set zcollectionID = zcustom2 where zcollectionID is null;

SQL is powerful and very useful but as always do a backup of the Database.pediadata file before you start as a single mistyped SQL command could delete information.