CODEWITHSUNDEEP
sqlvertica
Marco Fumagalli
Marco Fumagalli

Reputation: 2477

SQL Swapped values between columns

Due to import error I have this weird situation.

Suppose a table:

date         feat1   feat2
2019-01-01      a      z
2019-01-01      a      z
2019-01-03      z      a
2019-01-04      z      a
2019-01-05      z      a

From a certian date , we have values from feat1 and column feat2 swapped. Result I'm looking for

date         feat1   feat2
2019-01-01      a      z
2019-01-01      a      z
2019-01-03      a      z
2019-01-04      a      z
2019-01-05      a      z

How can I fix this in one update statement without creating a temp column? I'm using Vertica DB

Upvotes: 0

Views: 66

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1270653

Why not just do something like this?

update t
    set feat1 = t1.feat1,
        feat2 = t1.feat2
    from (select t.*
          from t
          order by t.date
          limit 1
         ) t1
    where t.feat1 <> t1.feat1 or t1.feat2 <> t1.feat2;

This does not require knowing the "magic date" or the specific values. If the values can be NULL, you would want to take that into account with the comparison logic.

If you just wanted a select query, the first_value() does what you want:

select t.date,
       first_value(t.feat1) over (order by t.date) as feat1,
       first_value(t.feat2) over (order by t.date) as feat1
from t;

Upvotes: 0

SAS
SAS

Reputation: 4045

Something like this would work in TSQL, you can use it as a base:

UPDATE tbl SET feat1 = feat2, feat2 = feat1 WHERE date >= 'YYYY-MM-DD'

Upvotes: 5

Related Questions