How to update column with multiple values

Question

I'm pretty new to PLSQL i want to know how i can update a column with multiple values. For example let's say i have a table called books that has a book name and book price, and in the beginning, I have only the price 300 for the book "C#". After the update, I need to have both 300 and 400 as prices for the book "C#"

       Book                          
Book_Name| Price        
---------|---------        
C#       | 300,400           
C++      | 500,600              
Java     | 700,800    

Answer 1

A column in a database table has one datatype, and it should have one value. Although if you really wanted to do it like that, you could use concatenation when updating, adding to any already existing characters like so (does not need PL/SQL, just normal sql):

UPDATE Book set Price = Price || ', '|| '400' where Book_Name = 'C#' ; 

By the way, if a book had no price, and you did this update, it would end up looking like this:

Book_Name| Price
---------|--------
C#       | ,400

To prevent this, add a CASE to not include a comma when there is already some other value in there:

UPDATE Book set Price = 
CASE WHEN Price IS NOT NULL 
THEN Price || ', '|| '400' 
else '400'
end
where Book_Name = 'C#' ; 

However, this design would not been very good. It would mean that in a column of a datatype for characters, you have placed numbers, hidden away inside.

It would be better instead to have multiple columns:

Book_Name| Price1   | Price2
---------|----------|--------
C#       | 300      |   400
C++      | 500      |   600
Java     | 700      |   800

The above is fine if you know the number of different prices you will have. That just depends on the problem you are trying to solve, on the business logic. If, however, you don't know the maximum number of possible prices, it would make sense to have a secondary table of prices for each book (you would need to have a distinct ID for each book, and based on that id the other table would have a list of all their prices) or perhaps change the existing table like so:

    Book_Name| Prices   
    ---------|----------
    C#       | 300 
    C#       | 400
    C++      | 500      
    C++      | 600      
    Java     | 700      
    Java     | 800

In this case, you would not have to update, but insert a new row each time:

INSERT INTO Book (Book_Name, Price) VALUES ( 'C#', 400) ;
INSERT INTO Book (Book_Name, Price) VALUES ( 'C++', 600) ;
INSERT INTO Book (Book_Name, Price) VALUES ( 'Java', 800) ;

Answer 2

As others already mentioned, it's bad practice to share multiple values in one column. If you really need multiple prices for one product(book) there are a few possible options: Make 2 Tables: Table1:

create table products
(  id number(5),
   name VARCHAR2(20 CHAR)
);
/
create table prices
(  id number(5),
   value number(6,2),
   product_id number(5),
   FOREIGN KEY (product_id)
        REFERENCES products(id) NOT NULL
)
/
insert into products values(1, 'C#');
insert into products values(1, 'C++');

insert into prices values(1, 300, 1);
insert into prices values(2, 400, 1);
insert into prices values(3, 700, 2);

That way, you can have multiple prices to one product and only need to save the product name oin one row, not multiple.

A second way to do this is with a relation table:

create table products
(  id number(5),
   name VARCHAR2(20 CHAR)
);
/
create table prices
(  id number(5),
   value number(6,2),
   product_id number(5)
)
/
create table product_price
(  product_id number(5),
   price_id number(5),
   FOREIGN KEY (product_id)
        REFERENCES products(id) NOT NULL,
   FOREIGN KEY (price_id)
        REFERENCES prices(id) NOT NULL
)
/
insert into products values(1, 'C#');
insert into products values(1, 'C++');

insert into prices values(1, 300);
insert into prices values(2, 400);
insert into prices values(3, 700);

insert into product_price values(1, 1);
insert into product_price values(1, 2);
insert into product_price values(2, 3);

Storing multiple values in a single column is always a bad idea as it makes it really hard to read the data and brings various other problems (how to update one value of the 4 contained?).

Always try to save each value only once in your database (i.e. the product name 'C#'). And look for the way that fits best to your solution. If you always got multiple prices that fit only for one product, I'd choose the first way with 2 tables. If you got multiple prices that match multiple products, I'd choose the relation table.

And if you only want to save the last price before you update the column, write a trigger to save it to a log table:

create trigger tu_tablename --tu = triggerUpdate
before insert on tablename
for each row
begin
   --writes the values before the insert to the log table
   insert into logTable values (:old.name, :old.price, sysdate);
   --logTable has columns product_name, old_price_value and date_change
end;
/

(please forgive possible syntax errors, see it as a challenge)

Answer 3

This is just the basic way of creating a stored proc and you can improve it by capturing error code after update. The command to stitch strings in oracle is ||.

Create or Replace Procedure upd_Book_Price (in_book_name varchar(10), 
in_newprice varchar(10));
IS
BEGIN

Update Book
Set Price = Price || ',' || in_newprice
Where Book_Name = in_book_name;

Commit;

End;

Answer 4

I'd suggest another - normalized - option (I've seen you asking what would that be?. There are many sources, this is what Wikipedia says), a child table which contains all prices for those books.

Although you can put two (or more prices) into a single column, believe us - that's a bad, bad choice.

Consider using something like this:

SQL> create table book
  2    (isbn   varchar2(13) primary key,
  3     name   varchar2(20) not null
  4    );

Table created.

SQL> create table price
  2    (id        number primary key,
  3     isbn      varchar2(13) constraint fk_pr_boo references book (isbn),
  4     date_from date not null,
  5     price     number
  6    );

Table created.

SQL>
SQL> insert all
  2    into book (isbn, name) values ('1-1234-124', 'C#')
  3    into book (isbn, name) values ('9-1244-332', 'C++')
  4    --
  5    into price (id, isbn, date_From, price) values (1, '1-1234-124', date '2018-01-01', 300)
  6    into price (id, isbn, date_From, price) values (2, '1-1234-124', date '2018-03-20', 400)
  7  select * From dual;

4 rows created.

SQL>
SQL> select b.isbn, b.name, p.date_from, p.price
  2  from book b left join price p on p.isbn = b.isbn
  3  order by b.isbn, p.date_from;

ISBN          NAME                 DATE_FROM       PRICE
------------- -------------------- ---------- ----------
1-1234-124    C#                   01.01.2018        300
1-1234-124    C#                   20.03.2018        400
9-1244-332    C++

SQL>

Answer 5

Create a trigger to hold the previous prices.

CREATE OR REPLACE TRIGGER trig_book
   BEFORE UPDATE OF price
   ON book
   FOR EACH ROW
BEGIN
   :new.price := :old.price || ',' || :new.price;
END;

Answer 6

This is never a good idea to have multiple values in the same column separated by some specific chars. The table should be normalized, or we can add a new nullable column like UpdatedPrice and employ it like:

ALTER TABLE BOOK
ADD COLUMN UpdatedPrice int
GO

UPDATE BOOK
SET UpdatedPrice = 400
WHERE Book_Name = 'C#'

In this way, you can have both price. In your code use Price when UpdatedPrice is null, otherwise use UpdatedPrice, and all will be good.