Create a new column in SQL table

Question

Create a new column in SQL table

Navigation

#1 by (1 votes)

1

I have a problem and I hope someone can help me ..

I have an SQL table with these columns:

What I need is to create a "Total" column that is unitPrice * amount, but I have tried several alter tables and they have not worked for me.

Try with this query: Alter Table invoice1 Add Field3 As (unit price * amount);

But I miss this error:

Any other way to do it?

mysql sql php

asked by David Bucci 04.12.2018 в 23:39

source

1 answer

php records are not saved Error iterating over array with ngFor

score 1 · Accepted Answer

First add a new column of type INT because at least with the data that you put all the results will be integers, also with AFTER you indicate after which column will add it:

ALTER TABLE precios add column totalFinal INT AFTER cantidad;

Later if you make a select you will get from your table this

select * from precios;
+--------------+----------+------------+
| precioUnidad | cantidad | totalFinal |
+--------------+----------+------------+
|            5 |       10 |       NULL |
|           14 |       10 |       NULL |
|           20 |       10 |       NULL |
|           30 |       10 |       NULL |
|           25 |       20 |       NULL |
+--------------+----------+------------+

As notes, the new column has a value of NULL , so now we must assign a new one with an update in this way:

UPDATE precios SET totalFinal = (precioUnidad * cantidad);

Now if you make a select again you will get

select * from precios;
+--------------+----------+------------+
| precioUnidad | cantidad | totalFinal |
+--------------+----------+------------+
|            5 |       10 |         50 |
|           14 |       10 |        140 |
|           20 |       10 |        200 |
|           30 |       10 |        300 |
|           25 |       20 |        500 |
+--------------+----------+------------+

Remember that as at update I did not put a condition of WHERE then it will do the multiplication in all rows

UPDATE

You must consider that, to store the data of your multiplication; uses:

INT if you are completely sure that all the results will be whole numbers, that is, without decimal value
DECIMAL if numbers with decimal punctuation will come out

USE OF DECIMAL DATA TYPE

If you are going to store a value of this type 127.56 then the declaration of your column should be:

DECIMAL(5, 2);

Where:

5 is the total number of spaces assigned

2 is the number assigned to store decimal values

3 will be the dedicated spaces for the numbers to the left of the decimal point

UPDATE NO. 2

MariaDB [demo]> CREATE SQL SECURITY INVOKER VIEW precioFinal AS SELECT *, (precioUnidad * cantidad) AS totalFinal FROM precios;
Query OK, 0 rows affected (0.054 sec)

MariaDB [demo]> SELECT * FROM precioFinal;
+--------------+----------+------------+
| precioUnidad | cantidad | totalFinal |
+--------------+----------+------------+
|            5 |       10 |       50   |
|           14 |       10 |       140  |
|           20 |       10 |       200  |
|           30 |       10 |       300  |
|           25 |       20 |       500  |
+--------------+----------+------------+
5 rows in set (0.001 sec)

Ignoring a recommendation from @Xeriff I tell you what better get the result from a VIEW that is a temporary result and that does not affect the original structure of the table in question, the Syntax above helps you get the desired value but over a column that is not going to be added to the precios table but is created on a view