How do i update existing records with new values where the values don't always exist.

[Phil Boguszewski]

Hi,

I have a database that is attached to a StratoDesk applicance and i need to perform an update on this database. I'm trying to update two columns:

`CODE` and `VAL`, i need to update this twice :-

CODE = 'IMAGE', VAL = "image_name"
CODE = IMAGE_UPDATE_MODE", `VAL` = "2"
Using this query:

UPDATE IGNORE CONFIGVALUE
JOIN CONFIGOBJECT on CONFIGOBJECT.COID=CONFIGVALUE.COID
JOIN CLIENTSTATE on CONFIGOBJECT.COID=CLIENTSTATE.COID
JOIN STATUSVALUE on CLIENTSTATE.EID=STATUSVALUE.EID
SET CONFIGVALUE.VAL = CASE CONFIGVALUE.CODE


WHEN 'IMAGE' THEN
"2.40.4139d-EEc-k418-x64-181112"
WHEN 'IMAGE_UPDATE_MODE' THEN
'2'
ELSE
CONFIGVALUE.VAL
END

WHERE CONFIGVALUE.CODE IN ('IMAGE','IMAGE_UPDATE_MODE','INSTALLED_IMAGE') AND CONFIGOBJECT.PARENT=3367 AND STATUSVALUE.VAL IN ('HP t520 Flexible Series TC');



I was able to do this on records in the database where the settings had already been modified. That is, say if i have 10 records, if i chose the image and update mode using the GUI in StratoDesk for 5 of those records, then the above values are updated. I can then update them again with new values no problem.
However if these settings have never been changed using the stratodesk GUI they do not appear in the database, so this would leave me with 5 records where the values for
`CODE` = 'IMAGE' `VAL' = 'IMAGE_NAME' and `CODE` = 'IMAGE_UPDATE_MODE' VAL ='2' do not exist in the database.

I've read about using INSERT...SELECT, INSERT....ON DUPLICATE KEYS and quite frankly am a little confused.

My end goal is to update these records to pick up the new 'image_name' and update mode and then these are not already present, insert the values. Is it possible to modify my current query to insert the values when missing, how should i be trying to write this query?

Thanks,
PhilB

Note: The columns "CODE" and "VAL" have a combination of hundereds of different values for each.

I don't know if i need to join the tables like i have done, but i need to use the 'PARENT' column from `CONFIGOBJECT` to make sure i get all records that are stored under one organisation unit.