Home > tips > Self update a table which have almost same column values

Self update a table which have almost same column values


Updating a table rows whose rows are same except for a few columns. For example all the rows are same except for insert datetime or status like columns.

UPDATE   schema1.table1 a
SET   col4 = ‘ABC’
WHERE  a.col3 <
ANY (SELECT   b.col3
FROM   schema1.table1 b
WHERE   a.col1 = b.col1
and a.col2=b.col2);

You can see update results by:

SELECT col1,col2,col3,col4 from  schema1.table1 where col1||col2 in (
SELECT col1||col2
FROM schema1.table1
group by col1,col2
having count(*)>1)

Advertisements
Categories: tips Tags: ,
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Don Charisma

because anything is possible with Charisma

Carol no Mundo!

Aventuras de uma intercambista a trabalho pelo mundo!

Blog do PHP

Oracle DBA for rookies!

nimaidba

Welcome to the world of Oracle with me....

Tech

News and reviews from the world of gadgets, gear, apps and the web

WordPress.com

WordPress.com is the best place for your personal blog or business site.

Gurcan Orhan's ODI and DWH Blog

Some words about Oracle Data Integrator and Data Warehousing.

%d bloggers like this: