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

Hi Peter thanks for the response.

I started writing a query to insert then on duplicate update but i was not able to get it to work.

This is where i am at the moment, the query doesn't work and i am at a loss as to how.

INSERT INTO CONFIGVALUE (CONFIGVALUE.CODE,CONFIGVALUE.VAL) VALUES ('IMAGE','image_name'), ('IMAGE_UPDATE_MODE','2')
SELECT CONFIGOBJECT.PARENT AS tmpPARENT, CONFIGOBJECT.COID AS tmpCOCOID, CONFIGVALUE.VAL AS tmpCVVALUE, CONFIGVALUE.CODE AS tmpCVCODE FROM CONFIGOBJECT
WHERE CONFIGOBJECT.PARENT IN
(
SELECT CONFIGOBJECT.PARENT FROM CONFIGVALUE JOIN CONFIGOBJECT ON CONFIGVALUE.COID=CONFIGOBJECT.COID WHERE CONFIGOBJECT.PARENT =15 AND CONFIGOBJECT.COTYPE > 2
)
ON DUPLICATE KEY UPDATE CONFIGOBJECT.COID=tmpCOCOID, CONFIGOBJECT.PARENT=tmpPARENT, CONFIGVALUE.VAL=tmpCVVALUE, CONFIGVALUE.CODE=tmpCVCODE


I've ran into a few different syntax errors while trying to modify the query and in all honesty I haven't got a clue anymore.

Should i be selecting everything from the table where the data does not exist, then insert into the database?

I'm concerned that just inserting the data will give me extra rows where i don't need them as opposed to inserting them for the rows i want. This made me think i would need to use some sort of sub query and maybe select into a temporary database then insert/update.

Could you provide some advice on this?

Thanks,

PhilB

Options: ReplyQuote




Sorry, you can't reply to this topic. It has been closed.

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.