SQL adding row if it does not already exist

Question

Say i have this table

Product	Version	Value
1	1	1000
2	1	2000
3_a	1	2500
3	1	3000
2_a	1	1200

What i want to do is:
For each row where product does not end with '_a':
check if there is any row with same product number that ends with '_a':
check if the value in that row is 60% of the value from the first row.

If there is no row like that, create it and increment version by 1

In logical terms:

For each row {
    If exists(concatenate(row.Product,'_a')) as row2 then {
        If not(row2.Value=row.Value*0.6) then {
            Create row, Product=row2.Product, Version=row2.version+1,Value=row.value*0.6
        }
    } Else {
        Create row, Product=concatenate(row.Product,'_a'), Version=1,Value=row.value*0.6
    }
}

I have tried this approach (with correct syntax) but don't know how to reference the original row to compare the values, or generate the Versions. The desiered result should be two new rows:

Product	Version	Value
1_a	1	600
3_a	2	1800

Using SQL Server 2005
Product can have duplicates, as long as version numbers are different.

Edit:
If the newest version of a Product ending with '_a' has an incorrect value, I would also like to add an even newer version with the correct value:

If I have: | Product | Version | Value | |---------|---------|-------| | 1 | 1 | 1000 | | 1_a | 1 | 600 | | 1_a | 2 | 1000 |

Then I would like to add: | Product | Version | Value | |---------|---------|-------| | 1_a | 3 | 600 |

Are you using an archaic unsupported version of SQL Server? (And if so why?). Or are you using MySQL? This is quite unclear. — Gordon Linoff
– Gordon Linoff, Commented Jul 23, 2021 at 15:13
I am indeed using the old, unsupported 2005 version of SQL Server (I don't have permission/authority to update company applications). — StarStreeto
– StarStreeto, Commented Aug 2, 2021 at 22:31

Serg · Accepted Answer · 2021-08-03 07:32:23Z

0

Try

insert tbl(Product, Version, Value)
select concat(Product, '_a'), 
      (select coalesce(max(t2.Version), 0) + 1 
       from tbl t2 
       where t2.Product = concat(t1.Product, '_a')),
       t1.Value * 0.6
from tbl t1
where right(t1.Product, 2) !='_a' 
   and not exists (select 1 
       from tbl t3 
       where t3.Product = concat(t1.Product, '_a')
           and t3.Value = t1.Value * 0.6
           and t1.Version < t3.Version);

Edit Not sure what is incorrect value of Version. Guess it is a version equal to or less then the original version, so added and t1.Version < t3.Version

edited Aug 3, 2021 at 7:32

answered Jul 23, 2021 at 10:47

Serg

22.8k5 gold badges23 silver badges50 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

StarStreeto Over a year ago

This worked very well, thank you so much! I am still having a small problem that I failed to specify, and which I cannot resolve by myself. I have added the requirement to the original question. Thank you for your help!

StarStreeto Over a year ago

This solved the issue, but created another. In the original example, the Value for Product '2_a' is 60% of the Value for Product '2'. But this query still creates a new row for Product '2_a' with the same value, and increments the version by 1 (it does this every time the query is run until there is a Version of Product '2_a' that exceeds the max Version of Product '2' (this would overflow the database very quickly)). What I want for the last row is something like "and t3.version = max(t3.version) group by Product". Hope this was clear enough.

StarStreeto Over a year ago

I only want to compare the highest Version of every Product. All other Versions can be viewed as archived Versions. Even though Product 'a_1' is derived from Product 'a', they should be viewed as separate Products. So I want to: 1,Find the highest Version of Product 'a'. 2,Find the highest Version of Product 'a_1'. 3,Compare their values. 4,If the Value of 'a_1' is not 60% of 'a':s Value. Add a new row as the query succeeds in doing.

StarStreeto · Accepted Answer · 2021-08-10 15:37:32Z

I solved it with this query. Thanks to Serg for providing a great start!

insert tbl(Product,Version,Value)
select concat(t1.product,'_a'),
    (select coalesce(max(t2.version),0) + 1
    from tbl t2
    where t2.Product = concat(t1.Product,'_a'),
    t1.value * 0.6
from tbl t1 inner join (
    select Product,max(Version) as Version
    from tbl
    group by Product
) as t3 on t3.Product = t1.Product and t3.Version = t1.Version
where right(t1.Product,2) != '_a'
    and not exists (select 1
        from tbl t4 inner join (
            select Product,max(Version) as Version
            from tbl
            group by Product
        ) as t5 on t5.Product = t4.Product and t5.Version = t4.Version
        where t4.Product = concat(t1.Product,'_a')
            and t4.Value = t1.Value * 0.6)

Overly complicated? Perhaps, but it works.

Collectives™ on Stack Overflow

SQL adding row if it does not already exist

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related