Update query without condition but need to update 90% of records in a table – SQL / SnowFlake

Update query without a condition but need to update a certain percentage of rows in the table for a specific column.

Say I have 100 rows in a table, I want update a specific column based but no specific condition; just that 90% of the records in the table has to have this new value that I want to update.

Any thoughts/ help is really appreciated.

Answer

You can use SAMPLE feature of SNOWFLAKE. Please see an example below.

CREATE OR REPLACE TABLE SAMPLE_TEST(ITEMID NUMBER, ITEMDESCRIPTION VARCHAR, STATUS VARCHAR);

INSERT INTO  SAMPLE_TEST VALUES(1,'BOOK',NULL);
INSERT INTO  SAMPLE_TEST VALUES(2,'TSHIRT',NULL);
INSERT INTO  SAMPLE_TEST VALUES(3,'CLOTHES',NULL);
INSERT INTO  SAMPLE_TEST VALUES(4,'KITCHEN',NULL);
INSERT INTO  SAMPLE_TEST VALUES(5,'SPORTS',NULL);
INSERT INTO  SAMPLE_TEST VALUES(6,'ELECTRONICS',NULL);
INSERT INTO  SAMPLE_TEST VALUES(7,'MEDICAL',NULL);
INSERT INTO  SAMPLE_TEST VALUES(8,'MAGAZINES',NULL);
INSERT INTO  SAMPLE_TEST VALUES(9,'SHAMPOO',NULL);
INSERT INTO  SAMPLE_TEST VALUES(10,'TOOTHPASTE',NULL);

SET COUNT=(SELECT COUNT(*)*0.9 FROM SAMPLE_TEST);
UPDATE SAMPLE_TEST SET STATUS ='A'
FROM (select ITEMID from SAMPLE_TEST SAMPLE($COUNT ROWS)) t1
WHERE SAMPLE_TEST.ITEMID = t1.itemid;