Thursday, December 24, 2009

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)

No comments:

Windows (powershell) counterparts of Linux commands

You may find Windows mostly powershell equivalent of frequently used Linux commands here. I will update this post, with newer ones by the ti...