query update with condition if

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

if-expression update

asked 05 Jul '19, 10:48

pignatale
11●1●1●2
accept rate: 0%

edited 08 Jul '19, 03:26

Volker Barth
40.5k●365●556●827

flat view

One Answer:

active answers oldest answers newest answers popular answers

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.

permanent link

answered 06 Jul '19, 08:08

Dev
81●1●1●4
accept rate: 20%

edited 08 Jul '19, 03:26

Volker Barth
40.5k●365●556●827

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

(08 Jul '19, 02:49) pignatale

Replies hidden

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

(08 Jul '19, 07:54) Mark Culp

wonderful it worked you are great thank you very much

(08 Jul '19, 11:37) pignatale

flat view

Your answer

toggle preview

community wiki:

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

*italic* or _italic_
**bold** or __bold__
link:[text](http://url.com/ "title")
image?![alt text](/path/img.jpg "title")
numbered list: 1. Foo 2. Bar
to add a line break simply add two spaces to where you would like the new line to be.
basic HTML tags are also supported

learn more about Markdown

Question tags:

update ×38
if-expression ×3

question asked: 05 Jul '19, 10:48

question was seen: 988 times

last updated: 08 Jul '19, 16:18

SAP SQL Anywhere

query update with condition if

Follow this question

Related questions