query update with condition if

[Former Member]

Hi, i have one problem with a query update for me more hard. the result of this select:

SELECT "DBA"."web_tracciato_prodotti_prestashop"."Riferimento ",
"DBA"."web_tracciato_prodotti_prestashop"."Caratteristica",
"DBA"."web_ktype_articoli"."ktype"
FROM ( "DBA"."web_tracciato_prodotti_prestashop" INNER JOIN "DBA"."web_articoli" ON "DBA"."web_tracciato_prodotti_prestashop"."ID" = "DBA"."web_articoli"."id" ) INNER JOIN "DBA"."web_ktype_articoli" ON "DBA"."web_articoli"."precodice" = "DBA"."web_ktype_articoli"."precodice" AND "DBA"."web_articoli"."articolo" = "DBA"."web_ktype_articoli"."articolo"

is correct. For each records of the web_tracciato_prodotti_prestashop table correspond n records of the web_ktype_articoli table because because to each web_tracciato_prodotti_prestashop.Riferimento correspond n records web_ktype_articoli.ktype. I have to update the web_tracciato_prodotti_prestashop.caratteristica field (initially null) with "Ktypes:" + value of web_ktype_articoli.ktype.

I'm a beginner and I've tried it but it doesn't work:

update dba.web_tracciato_prodotti_prestashop
If dba.web_tracciato_prodotti_prestashop.Caratteristica <>  Null
    then 
        set Caratteristica  = ", " & DBA.web_ktype_articoli.ktype
    else 
        set Caratteristica  = "KTypes: " & DBA.web_ktype_articoli.ktype 
end if
FROM ( "DBA"."web_tracciato_prodotti_prestashop" inner JOIN "DBA"."web_articoli" ON "DBA"."web_tracciato_prodotti_prestashop"."ID" = "DBA"."web_articoli"."id" ) inner JOIN "DBA"."web_ktype_articoli" ON "DBA"."web_articoli"."precodice" = "DBA"."web_ktype_articoli"."precodice" AND "DBA"."web_articoli"."articolo" = "DBA"."web_ktype_articoli"."articolo"

HELP ME PLEASE

[former_SQLA_member1694950 · Accepted solution]

update 
    dba.web_tracciato_prodotti_prestashop 
    set Caratteristica = 
    (If dba.web_tracciato_prodotti_prestashop.Caratteristica is not  Null then

        ', ' + DBA.web_ktype_articoli.ktype 
    else 
        'KTypes: ' + DBA.web_ktype_articoli.ktype 
    end if) 
FROM 
    "DBA"."web_tracciato_prodotti_prestashop" 
inner JOIN "DBA"."web_articoli" ON "DBA"."web_tracciato_prodotti_prestashop"."ID" = "DBA"."web_articoli"."id"
inner JOIN "DBA"."web_ktype_articoli" ON "DBA"."web_articoli"."precodice" = "DBA"."web_ktype_articoli"."precodice" AND "DBA"."web_articoli"."articolo" = "DBA"."web_ktype_articoli"."articolo"

Please have a look if this is what you were needing. Table names were so hard to read so I have not checked the where condition just corrected the query.

[Former Member · Accepted solution]

Good morning Thank you for your answer the syntax gives me an error SQLCODE 157 Cannot convert 'Ktypes:' to a numeric the Caratteristica field in sql is text I don't understand why it generates this error

[MarkCulp · Accepted solution]

You are using the arithmetic addition operator... Switch the + to || (string concatenation).

[Former Member · Accepted solution]

wonderful it worked you are great thank you very much