How to merge multiple update query as a single update query?-CodePudding

I have a scenario to update 'NULL' string if column has no value that means null. So, i ran multiple update query to set the 'NULL' string.

update codesets set system_name='NULL' where system_name is null
update codesets set column_name='NULL' where column_name is null
update codesets set table_name='NULL' where table_name is null
update codesets set schema_name='NULL' where schema_name is null

How to merge multiple update query as a single update query ?

CodePudding user response：

You could use coalesce to set the values to 'NULL', but I don't see the point in doing that, the code seems to be less readable this way (in my opinion).

Here's an example code:

update codesets 
set 
system_name=coalesce(system_name, 'NULL'),
column_name=coalesce(column_name, 'NULL'),
table_name=coalesce(table_name, 'NULL'),
schema_name =coalesce(schema_name , 'NULL') 
where 
system_name is null or column_name is null or table_name is null or schema_name is null

And also, why do you need to set the values to 'NULL'?

CodePudding user response：

You can use case expressions, or nvl, or coalesce:

update codesets
set system_name=coalesce(system_name, 'NULL'),
    column_name=coalesce(column_name, 'NULL'),
    table_name=coalesce(table_name, 'NULL'),
    schema_name=coalesce(schema_name, 'NULL')
where system_name is null
or column_name is null
or table_name is null
or schema_name is null

For each row that is updated, each of those column is (re)set to its current value if it is not null, and to 'NULL' if it is.

The combined where clause means that only rows where at least one column needs to be updated are touched; for those rows all four columns are updated, but might be to their original values. At least one column will change though.

fiddle

I'd question whether this is a good idea though - using the magic value 'NULL' instead of a true null seems unhelpful.

CodePudding user response：

You can simply update all rows and let COALESCE set the text 'NULL' where it finds NULL.

The exclusion of all rows rows that have data on it, will not be faster, as the the database must check every row anyway and will skip if it finds that it must update the same value

UPDATE codesets 
SET 
system_name=COALESCE(system_name, 'NULL'),
column_name=COALESCE(column_name, 'NULL'),
table_name=COALESCE(table_name, 'NULL'),
schema_name =COLASCE(schema_name , 'NULL')