Sql-server – Replace all column values with other values in the same column according to primary key

queryreplacesql serversyntax

I'm querying the following table:

id	value	applicable
id1	5	Unknown
id1	5	Unknown
id1	6	Yes
id 2	5	Unknown
id 2	5	No
id 2	3	Unknown

I would like to replace all "Unknowns" in the applicable column, with the non-unknown values pertaining to each id.

Ideally, I would like to see this:

id	value	applicable
id1	5	Yes
id1	5	yes
id1	6	Yes
id 2	5	No
id 2	5	No
id 2	3	No

Sorry if this a very easy question, I'm new to this!

Thank you

Best Answer

This expects one one value besides of unknown, if this si not the case you must choose another aggregation function

CREATE TABLE table1
    ([id] varchar(4), [value] int, [applicable] varchar(7))
;
    
INSERT INTO table1
    ([id], [value], [applicable])
VALUES
    ('id1', 5, 'Unknown'),
    ('id1', 5, 'Unknown'),
    ('id1', 6, 'Yes'),
    ('id 2', 5, 'Unknown'),
    ('id 2', 5, 'No'),
    ('id 2', 3, 'Unknown')
;
GO

UPDATE table1
SET    table1.[applicable]  = t1.[applicable] 
FROM   table1
       INNER JOIN (SELECT [id],MIN([applicable]) as [applicable] FROM table1  WHERE  [applicable] <> 'Unknown' GROUP BY [id]) t1
         ON table1.id = t1.id

GO

SELECT * FROM    table1
GO

id   | value | applicable
:--- | ----: | :---------
id1  |     5 | Yes       
id1  |     5 | Yes       
id1  |     6 | Yes       
id 2 |     5 | No        
id 2 |     5 | No        
id 2 |     3 | No

db<>fiddle here

Related Solutions

Sql-server – use SPARSE somehow on a non-nullable bit column with mostly false values

A filtered index (WHERE IsMostRecentRun = 1) sounds like a better idea to me than using sparse. If you can make it so that false is instead represented by null, you may be able to do both, but while that will potentially save some space in the base table, I suspect the bigger gain would be in query performance from the filtered index - as long as it's covering. If you need too many columns to cover and/or need to also filter or join on other columns, then you may find a balancing act between the improvements you get from the seek or range scan on the filtered index and the costs of things like lookups to get at the rest of the columns.

That all said, it seems suspicious you would need an additional column to serve as a flag for this. Isn't this information redundant? Can't it be determined by other data? In which case I would focus on index tuning to optimize the use of the existing columns instead of storing and maintaining redundant data.

(Also I don't think Pinal is correct.)

Sql-server – updating the entire column with datetime values based on other values in the row

It looks like you have at least two different regional date formats there, possibly USA and Europe? You could just split the updates by languages...

something like this, which uses the language setting to control isdate and convert.

set language english;
update test_dates 
set ToDateTime = convert(datetime,ContainedData)
where AID='123' and PID='2' and isdate(ContainedData)=1;

set language british;
update test_dates 
set ToDateTime = convert(datetime,ContainedData)
where AID='123' and PID='2' and isdate(ContainedData)=1;

If the table is too large to update in big chunks, then you should look at wrapping that in a loop with a row limit and a transaction around each update and repeat until zero rows have been updated.

Best Answer

Related Solutions

Sql-server – use SPARSE somehow on a non-nullable bit column with mostly false values

Sql-server – updating the entire column with datetime values based on other values in the row

Related Question