CODEWITHSUNDEEP
sqldatabasesnowflake-cloud-data-platformupsert
314mip
314mip

Reputation: 403

Snowflake table replace nulls with multiple values

I have database table at Snowflake, with NULL values.

id month
a01 5
a02 6
b01 6
b04 NULL

I need transform it as this example:

id month
a01 5
a02 6
b01 6
b04 7
b04 8

I must replace NULLs with multiple values (with 2 values: 7 and 8 - summer months). So from each NULL row I need to make two rows.

Upvotes: 1

Views: 949

Answers (1)

buddemat
buddemat

Reputation: 5301

The simplest way to do this would be with two separate statements, one to insert an extra row and one to update the existing NULL value:

INSERT INTO tab (id, month) 
SELECT id, 8 as month
FROM tab
WHERE month is NULL;

followed by

UPDATE tab 
SET month = 7
WHERE month is NULL;

Result:

id month
a01 5
a02 6
b01 6
b04 7
b04 8

See this db<>fiddle.

Upvotes: 3

Related Questions