0

How do I update/replace the value of the first table from the list of my second table in SQL. Sorry im not so good in using replace() of SQL especially replacing from values base from different table

First table.

ID | Value
======================
 1 | Fruits[Apple]
 2 | Fruits[Apple,Mango]
 3 | Apple[Red,Green]

Second table

Search | Replace
=========================
Apple  | Orange
Green  | Yellow
1
  • Typically I would use replace(). In your example do you want all instances of the word apple replaced? If you did both substitutions Row 3 would end up being: Orange[Red, Yellow] Jan 10, 2017 at 8:43

2 Answers 2

1

In Oracle SQL it goes like this :

UPDATE FIRST_TABLE FT
   SET FT.VALUE =
       (SELECT ST.REPLACE FROM SECOND_TABLE ST WHERE FT.VALUE = ST.SEARCH)

But for this this to work properly, keep in mind that the attribute Search in the SECOND_TABLE must be unique, otherswise you'll need to rearange your query.

0

SELECT REPLACE(value,'apple','orange') from table1

Or to update:

Update table1 set value = REPLACE(value, 'apple', 'orange')

If you have multiple substitutions you could use a cursor and variables.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.