SQL Help!

[PMKeates]

Hi,

I would like to update a table but am not sure of the most effecient way to do it for this scenario:

What I want to do is set column 4 = II where it's shaded grey.

The logic behind it is to update records where for a set combination of column 2 and column 3 there are zero records with column 4 = II.

 

[PMKeates]

Hi all

Thanks for the feedback!

To clarify, there are 100m rows in this table, with around 50k unique values in column 2, 10k unique values in column 3 (though not all are present for every column 2 value, as in the example, and there's no pattern) and a dozen unique values for column 4! The table is keyed on column 1, and there can be valid duplications of column 2 + 3 + 4 - the column 1 key joins to another table with further information that allows you to resolve the duplicates, but that's not important for now. This is a 20 year old database structure with tens of billions of records in total (hence why it's still around!), so don't blame me for it

That all unfortunately means the queries with IN ('x', 'y') etc. are out of the question - I really need the logicality of it rather than the absolute according to the table I showed in the opening post.

From the wording I assume that it's just the value of column 4 that is the key, i.e. whatever is in columns 2 and 3, then if, for the combination of values, there is no entry with a II in column 4 then do the update.

Absolutely correct - the values in column 2 and 3 are effectively irrelevant, it's just their combination that's important - if for that combination there is no column 4 value equal to 'II', then set column 4 equal to 'II' where it is 'I' for that combination.

Looking at your SQL, could I simply set column 4 = 'II' where column 4 = 'I' and the column 1 from your select query?

Cheers!