[TIP] Combining columns in sql

Talk to other Pedia users about the programs, share tricks and tips or ask questions about existing features.

[TIP] Combining columns in sql

Postby noisyscott » Sat Feb 09, 2008 7:37 pm

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.
noisyscott
Addicted to Bruji
Addicted to Bruji
 
Posts: 40
Joined: Mon Feb 12, 2007 10:48 am

Postby Conor » Sun Feb 10, 2008 7:05 am

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.
User avatar
Conor
Top Dog
 
Posts: 5206
Joined: Sat Jul 03, 2004 12:58 pm


Return to PediaTalk

cron