Updating a SQL table

Question

I am doing a Java program in which I am reading input from some files and if it contains any string that I have in my list I have to update the table with name and its count. First i created a table as follows

 create table mobile(name varchar2(20),count int,primary key(name));

for instance,if I read string like "Sony unviels its new phone", table must be updated with name as sony count as 1.

what my doubt is initially it is an empty table. Can we update it as I said.

Thanks in advance....

Of course you can, change int to number for count column name. And count is a reserved key word in Oracle. — Jacob, May 29 '14 at 08:35

Jacob · Answer 1 · 2014-05-29T08:48:22.150

1

First table creation

CREATE TABLE mobile
(
   t_name    VARCHAR (20),
   t_count   NUMBER,
   PRIMARY KEY (t_name)
);

And insert statement

INSERT INTO mobile
     VALUES ('Sony', 1);

Single statement for insert and update

MERGE INTO mobile t
     USING (SELECT *
              FROM mobile
             WHERE LOWER (t_name) = 'sony') s
        ON (t.t_name = s.t_name)
WHEN MATCHED
THEN
   UPDATE SET t.t_count = t_count+1
WHEN NOT MATCHED
THEN
   INSERT     (t_name, t_count)
       VALUES ('sony', 1);

edited May 29 '14 at 08:48

answered May 29 '14 at 08:40

Jacob

14,463
65
207
320

Is this what you are looking for? – Jacob May 29 '14 at 08:41
@Polppan...thankz...but i want an update stmt as when I have a string which again contains sony I have to update it so i want a single query to do all tis – Reddevil May 29 '14 at 08:43

score 0 · Answer 2 · answered May 29 '14 at 08:41

0

Either you prepopulate you table with your given word list so something like

CREATE TABLE mobile(
  name varchar2(20),
  count_column int,
  primary key(name)
);

INSERT INTO mobile (name, count_column) VALUES 
('Sony', 0);

or you are adding some magic to your Java code which is deciding whether to do an UPDATE or an INSERT.

As an simple would look like theoretical

 UPDATE mobile SET count_column to count_column+1 where name = 'Sony';

this will fail if there is none entry given.

answered May 29 '14 at 08:41

frlan

6,950
3
31
72

@frlan...thankz for ur reply...what i am asking is can we do that update command when the table is empty.. – Reddevil May 29 '14 at 08:45
The answer is: If you are not adding a procedure doing the decision insert/update for you, most likely not. – frlan May 29 '14 at 08:55

score 0 · Answer 3 · edited May 23 '17 at 12:28

0

You will have to execute an UPSERT like query , since oracle doesn't have it, you have to do a workaround to resolve this. please check this .

edited May 23 '17 at 12:28

Community

1
1

answered May 29 '14 at 09:08

Mifmif

3,132
18
23

Updating a SQL table

3 Answers3