Make an "Update" with sum of a number that is as character

Asked

Viewed 737 times

-1

I need to perform a update summed in a table whose column is of the type text. Example: 

UPDATE produto SET codigo = 'codigo'+ 1;

I have the following return:

UPDATE produto SET codigo = 'codigo'+ 1;
ERROR:  invalid input syntax for integer: "codigo"
LINHA 1: UPDATE produto SET codigo = 'codigo'+ 1;

If I’m wrong please tell me the correct method.

sql postgresql sql-update summing-up

  • the way you described you are concatenating the string code with 1.. would then have 'code 1' save at the bank. That’s what you want?

  • no friend, I actually need to add the existing value you understand, like I have code 361 and I want it to add +1. and repeat this for all codes..

  • I think it is not very clear, but it may be related to: https://answall.com/a/31633/69359 and https://answall.com/a/296101/69359

  • @Erenilsonalencastro Did any of the answers solve your question? Do you think you can accept one of them? Check out the [tour] how to do this, if you haven’t already done so. You would help the community by identifying what was the best solution for you. You can accept only one of them. But you can vote on any question or answer you find useful on the entire site (when you have enough score).

3 answers

2

It’s very simple, turn the column into a numeric type.

Use text for descriptive columns, even if you only have numeric digits in it. And use numeric columns when you need to do accounts, like this.

It’s even possible to do it this way, but it’s so much more complicated that the ideal is to change the spine. Don’t work with misconceptions that the problem gets bigger and bigger.

If you insist you will need first of all to convert the column to numeric, make the account summing 1 and then convert again to text.

UPDATE produto SET codigo = to_char(to_number(codigo, '999999') + 1);

I put in the Github for future reference.

But this may vary according to your need.

Understand the difference between what is the data, what is the column, the syntax, what does everything in the code. Without understanding what each thing does in your code you won’t be able to program. And this is the easy part of programming. You have no idea the hard part. And how easy it is to do everything wrong and work and think you’re getting some result, when you’re causing huge problems.

  • Thanks for the return, but in my case it did not work: UPDATE product SET code = to_char(to_number(code, '361') + 1); ERROR: Function to_char(Numeric) does not exist LINE 1: UPDATE product SET code = to_char(to_number(code, '361')... TIP: No Function Matches the Given name and argument types. You Might need to add Explicit type Casts.

0

You can do it like this:

  UPDATE produto SET codigo = produto.codigo + 1;

but remember that you will be adding + 1 to all your codes as you are not passing any condition to the UPDATE.

  • Good morning thanks for the return, but I have the following error: UPDATE product SET code = product.code + 1; ERROR: Operator does not exist: text + integer LINE 1: UPDATE product SET code = product.code + 1;

  • @Erenilsonalencastro can post the table structure?

-3


Solved, I did it this way: CREATE TEMPORARY SEQUENCE Code START WITCH 361;

AFTERWARD:

UPDATE product set code= nextval('product code');

  • It’s nice of you to come and share the result. The only problem in the current case is that the answer, despite having solved in its specific scenario, does not produce exactly what was asked in the question (and if it produced in your case, it is almost a coincidence).

Browser other questions tagged sql postgresql sql-update summing-up

You are not signed in. Login or sign up in order to post.