CODEWITHSUNDEEP
sqloracle-database
Norie Cris Sagun
Norie Cris Sagun

Reputation: 58

Script for updating table after checking it row by row

I have a table with four columns. Column3 should be equal to Column1 and Column2 is always equal to Column4. Please give me a script that will check my table row by row and upon checking, if there are rows in column1 and column3 that are not equal, it will update rows in column3 so it will be equal to column 1. our database is oracle 9i and i have little experience with sql so im hoping that you can help me out.

This is sample data of table1 PACKAGE_DEALS (but this table consists of 5 more columns)

ID_NO   | ID_NAME 
--------+--------
1886103 | BMW 
1884247 | Ferrari 
3177964 | Honda 
 381677 | Volvo

And this is for table2 INDIVIDUAL_DEAL (this table also has two 4 more columns)

PLAN_ID | PLAN_NAME 
--------+----------
3177964 | Honda 
3177957 | Honda 
1886103 | BMW 
1886103 | BMW 
1884247 | Ferrari 
1884247 | Ferrari 
 381436 | Volvo 
 381677 | Volvo

And I would like a script that will make table2 like this.

PLAN_ID | PLAN_NAME 
--------+----------
3177964 | Honda 
3177964 | Honda 
1886103 | BMW 
1886103 | BMW 
1884247 | Ferrari 
1884247 | Ferrari 
 381677 | Volvo 
 381677 | Volvo

The PLAN_ID in table2 should always be equal to the ID_NO in table1. Table2 is used often and many have accessed to it, so there are datas that are not correct so I would like to have a script that will update it and have right datas in place.

update table1
set col3 = (select col1 from table2 where key2 = table1.key1)
where col3 <> (select col1 from table2 where key2 = table1.key1);

i have used the update script above given by thorsten (and I'm thankful for that) and it says too many rows to be updated. is there another way to do this?

Upvotes: 0

Views: 83

Answers (1)

Thorsten Kettner
Thorsten Kettner

Reputation: 94914

You don't need a script for this. A simple UPDATE statement will suffice. It will update all records WHERE a certain condition is met (column1 being different from column3 in your case).

Just try to write it yourself. You will see how easy this is. If you happen to have issues with this though, then write again and tell us what particular problem you are facing.

EDIT: From your comments it shows that you didn't describe your problem correctly. You are not talking of a table where one column doesn't match the other, but a query where one column doesn't match the other. What you want to do is more or less this:

update table1
set col3 = (select col1 from table2 where key2 = table1.key1)
where col3 <> (select col1 from table2 where key2 = table1.key1);

So you'd write the same query twice, one in the SET clause, once in the WHERE clause.

For this situation Oracle offers updateble queries:

update 
(
  select table1.col3, table2.col1
  from table1 
  join table2 on (table1.key1 = table2.key2)
)
set col3 = col1
where col3 <> col1;

This is only possible when the dbms sees it as guaranteed that the query won't retrieve more than one result record per table1 record. Sometimes it happens that you know this to be guaranteed, but Oracle doesn't. It depends on unique indexes and complexity probably. So good luck here.

If the updatable query approach fails, there is still the option with having the query in both the SET and WHERE clause.

Upvotes: 3

Related Questions