CODEWITHSUNDEEP
sqlsql-updateoracle-sqldeveloper
Jordi Castilla
Jordi Castilla

Reputation: 26961

Update column with String prefix

Im triying to UPDATE a column named INV_SUPPLIER, I must add ES prefix to INV_SUPPLIER if the column does not start with ES AND does not contain NO_VALIDO AND IS NOT NULL

Column declaration: Nullable Default INV_SUPPLIER VARCHAR2(32 BYTE) Yes

When I select:

SELECT * 
    FROM 
        EFAC_INVOICE f 
    WHERE 
        f.INV_SUPPLIER NOT LIKE 'ES%' 
    AND 
        f.INV_SUPPLIER NOT LIKE 'NO_VALIDO'
    AND 
        f.INV_SUPPLIER IS NOT NULL;

Seems to give me the correct data.

But when I tri to update: 

UPDATE EFAC_INVOICE f 
    SET 
        f.INV_SUPPLIER='ES'+f.INV_SUPPLIER 
    WHERE 
        f.INV_SUPPLIER NOT LIKE 'ES%' 
    AND 
        f.INV_SUPPLIER NOT LIKE 'NO_VALIDO'
    AND 
        f.INV_SUPPLIER IS NOT NULL;

But throws me the error:

Error SQL: ORA-01722: número no válido
01722. 00000 - "invalid number"
*Cause: The specified number was invalid.
*Action: Specify a valid number.

Any idea where I'm wrong???

Upvotes: 0

Views: 1264

Answers (1)

xQbert
xQbert

Reputation: 35323

use || to concat in oracle. use + in SQL server. you also have an extra ; before your last check

Upvotes: 2

Related Questions