How do i update existing records with new values where the values don't always exist.
Posted by: Phil Boguszewski
Date: January 14, 2019 03:46AM

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.

Options: ReplyQuote


Subject
Written By
Posted
How do i update existing records with new values where the values don't always exist.
January 14, 2019 03:46AM


Sorry, only registered users may post in this forum.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.