How to update multiple records in a table?

Question

I need to update the column B in a table, which has a column A as the primary key, with the a different value for each value in column A. There are about 50,000 rows to be updated in the table, which makes it impossible to do this manually. Is there any other way to update it?

Of all the records in the table, I want to update just 50000. For each record among these 50,000, the value to be updated is different. How can I update the table without having to write 50,000 update queries?

Column A.   Column B
  One.             1
  Two              2
  Three            3

I want to update one=4, two=5 and so on for about 50,000 rows.

Thanks in advance guys!

show the sampe for "different value for each value in column " .. — ScaisEdge, Dec 03 '15 at 10:19
It is unclear what you exactly want to do but to update a row.... To update the whole table, don't have the WHERE clause. Update YourTable SET B = "NewValue" WHERE A = "KeyValueOfRowToUpdate" — AntDC, Dec 03 '15 at 10:19
The basis for updating column B is unclear in your question. `UPDATE table SET B='some-value' WHERE A='pk-value'` is the standard way to update based on primary key. `UPDATE table SET B=A*2` would be an example of setting column B to twice of value in column A, for all rows. Just throwing some ideas. — Web User, Dec 03 '15 at 10:45
Is column A (the primary key column) not a numeric/auto-increment/ column? — Web User, Dec 03 '15 at 10:47
Have you considered using IMPORT with INSERT_UPDATE option? You could have your new values in a file and this way automate the "manual" updates. — data_henrik, Dec 03 '15 at 10:47
how many dirrenet values are there for "I want to update one=4, two=5 and so on for about 50,000 rows." — Avrajit Roy, Dec 03 '15 at 11:41
Why did you tag this with `plsql`? Do you want a stored procedure? — , Dec 03 '15 at 14:22
The values in column B are not in any particular pattern . for example, After the update, I want one= 10, two=4 ,three= 1000. I have all the primay key values( i.e. column A) of all the rows and their corresponding column B values in an excel sheet. Does this help ? — Vipul Reddy, Dec 07 '15 at 06:20

score 0 · Answer 1 · edited Dec 03 '15 at 14:22

I don't know whether I got your requirement properly but i have written a below working snippet to replicate the scenario. Let me know if this helps

--Drop any existing table if present with same name
DROP TABLE SIMPLE_UPDATE;


--Create new table
CREATE TABLE SIMPLE_UPDATE
(
COL1 NUMBER,
COL2 VARCHAR2(2000 CHAR)
);

-- Inserting random test data
INSERT INTO SIMPLE_UPDATE
SELECT LEVEL,TO_CHAR(TO_DATE(LEVEL,'J'),'JSP') FROM DUAL
CONNECT BY LEVEL < 500;

-- Updating the col2 value assuming thta the increment is adding 3 to each number and updating the col2 with the same.

UPDATE SIMPLE_UPDATE
SET COL2 = COL1+3
WHERE <COL_NAME> = <CONDITON>;

COMMIT;

How to update multiple records in a table?

1 Answers1