Fixing Duplicate Columns SQL

Question

I have the following code:

update tble
set 
offer_id_02 = offer_id_03,
offer_id_03 = offer_id_04,
offer_id_04 = offer_id_05,
offer_id_05 = offer_id_06,
offer_id_06 = offer_id_07,
offer_id_07 = offer_id_08,
offer_id_08 = NULL
where offer_id_02 = offer_id_01;


update tble
set 
offer_id_03 = offer_id_04,
offer_id_04 = offer_id_05,
offer_id_05 = offer_id_06,
offer_id_06 = offer_id_07,
offer_id_07 = offer_id_08,
offer_id_08 = NULL
where offer_id_03 = offer_id_01 or offer_id_03 = offer_id_02; 


update tble
set 
offer_id_04 = offer_id_05,
offer_id_05 = offer_id_06,
offer_id_06 = offer_id_07,
offer_id_07 = offer_id_08,
offer_id_08 = NULL
where offer_id_04 = offer_id_01 or offer_id_04 = offer_id_02 or offer_id_04 = offer_id_03;


update tble
set 
offer_id_05 = offer_id_06,
offer_id_06 = offer_id_07,
offer_id_07 = offer_id_08,
offer_id_08 = NULL
where offer_id_05 = offer_id_01 or offer_id_05 = offer_id_02 or offer_id_05 = offer_id_03 or offer_id_05 = offer_id_04;


update tble
set 
offer_id_06 = offer_id_07,
offer_id_07 = offer_id_08,
offer_id_08 = NULL
where offer_id_06 = offer_id_01 or offer_id_06 = offer_id_02 or offer_id_06 = offer_id_03 or offer_id_06 = offer_id_04 or offer_id_06 = offer_id_05;


update tble
set 
offer_id_07 = offer_id_08,
offer_id_08 = NULL
where offer_id_07 = offer_id_01 or offer_id_07 = offer_id_02 or offer_id_07 = offer_id_03 or offer_id_07 = offer_id_04 or offer_id_07 = offer_id_05  or offer_id_07 = offer_id_06;


update tble
set 
offer_id_08 = NULL
where offer_id_08 = offer_id_01 or offer_id_08 = offer_id_02 or offer_id_08 = offer_id_03 or offer_id_08 = offer_id_04 or offer_id_08 = offer_id_05 or offer_id_08 = offer_id_06 or offer_id_08 = offer_id_07;

Essentially, I have a SQL table with a customer and 8 offers they get as a promotion. Due to bad logic, some duplicates come through. To fix this I "scoot over" the duplicate offer and null out offer_id_08 slot. The issue is, the logic is different depending on which offers are duplicate to each other. How can I build this out in a concise way?

Answer 1

Maybe, as a first step, you could try to merge your queries into one big query, using CASE's and combined WHERE's:

UPDATE table
SET offer_id_02 = 
   CASE 
      WHEN offer_id_02 = offer_id_01 THEN offer_id_03
      ELSE offer_id_02
   END,
   offer_id_03 = 
   CASE 
      WHEN offer_id_03 = offer_id_01 OR offer_id_03 = offer_id_02 THEN offer_id_04
      ELSE offer_id_03
   END,
  ...
  offer_id_08 = NULL
WHERE 
     offer_id_01 IN (offer_id_02, offer_id_03, offer_id_04, offer_id_05, offer_id_06, offer_id_07, offer_id_08)
  OR offer_id_02 IN (offer_id_03, offer_id_04, offer_id_05, offer_id_06, offer_id_07, offer_id_08)
  ...
  OR offer_id_07 = offer_id_08;

Answer 2

This is a trivial way to reformat (un-pivot, flatten) your table as @JNevill suggests, 4 repeating attributes per record for an illustration.

create table the_table (
 id serial, 
 attr_1 integer, 
 attr_2 integer,
 attr_3 integer,
 attr_4 integer
);

insert into the_table (attr_1,attr_2,attr_3,attr_4) 
values 
 (10, 11, 12, 13),
 (20, 21, 22, 23),
 (30, 31, 32, 33);

select t.id, l.*
from the_table t
cross join lateral (
 select 'attr_1', attr_1 union all
 select 'attr_2', attr_2 union all
 select 'attr_3', attr_3 union all
 select 'attr_4', attr_4
) as l(attr_name, attr_value);

You can shape the abobe as a CTE and then select distinct on (id, attr_value).

DB Fiddle

Edit
The above is a PostgreSQL example. Under MSSQL, SnowFlake and Spark that support native UNPIVOT clause, it is simpler.