Databases
StackOverflow2013
Postsdbo
POHow to apply kind of loop for this Oracle query?
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POHow to apply kind of loop for this Oracle query?
    text
    copied!<p>I have a query that was developed using Oracle. I want update the same column </p> <p>'5' times. below the query that i developed:</p> <pre><code>MERGE INTO product pr USING( SELECT pr.uuid, xmltype(pr.attributes_de_de).extract('//attr[@name = "SellingPoint1"]/string/text()') AS sellingpoint1, xmltype(pr.attributes_de_de).extract('//attr[@name = "SellingPoint2"]/string/text()') AS sellingpoint2, xmltype(pr.attributes_de_de).extract('//attr[@name = "SellingPoint3"]/string/text()') AS sellingpoint3, xmltype(pr.attributes_de_de).extract('//attr[@name = "SellingPoint4"]/string/text()') AS sellingpoint4, xmltype(pr.attributes_de_de).extract('//attr[@name = "SellingPoint5"]/string/text()') AS sellingpoint5 FROM product pr WHERE pr.defaultproductvariationid ='1tap_vEBvuEAAAE89CgjnPbb' AND pr.typecode = '16' ) defaultproducts ON (pr.uuid = '8d2p_vEBCJgAAAE8ruYjnPba') WHEN MATCHED THEN UPDATE SET pr.attributes_de_de = CASE WHEN sellingpoint1 IS NOT NULL THEN CASE WHEN (SELECT count(1) existscount FROM product pr WHERE pr.uuid = '8d2p_vEBCJgAAAE8ruYjnPba' AND existsNode(xmltype(pr.attributes_de_de), '/attrs/attr[@name="SellingPoint1"]') = 1) = 1 THEN UPDATEXML(XMLTYPE.createXML(pr.attributes_de_de),'/attrs/attr[@name = "SellingPoint1"]/string/text()', sellingpoint1).getClobVal() ELSE APPENDCHILDXML(xmltype(pr.attributes_de_de), 'attrs/attr[@name="SellingPoint22"]', XMLType('&lt;string&gt;test&lt;/string&gt;')).getClobVal() END ELSE DELETEXML(xmltype(pr.attributes_de_de), '/attrs/attr[@name="SellingPoint1"]').getClobVal() END DELETE where pr.uuid != '8d2p_vEBCJgAAAE8ruYjnPba' </code></pre> <p>the challenge in this query is the column 'pr.attribute_de_de' should update for sellingpoint1, sellingpoint2, sellingpoint3, sellingpoint4, sellingpoint5. How this can be done in oracle. Thank you very much for any suggestions</p>
 

Querying!

 
Guidance
An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload