SQL Case Statement

-
The SQL CASE Statement is a very handy little feature. 

 I recently had to update several rows of a table at once and i used it to good effect. I have no idea how it will perform when it comes to large updates but for a small amount of rows it works like a charm.

 Picked up from this SO thread.


Multiple update

UPDATE config
   SET config_value = CASE config_name 
                      WHEN 'name1' THEN 'value' 
                      WHEN 'name2' THEN 'value2' 
                      ELSE config_value
                      END
 WHERE config_name IN('name1', 'name2');

Comments

Post a Comment

Popular posts from this blog

OneNote Code Format plugin

-
If you are using OneNote like me to keep your shit together and ever wondered why this does not have a built in code formatting option, then your worry days are over. No one wants to see their code lying around without formatting in plain text form , so make use of this plugin to make things prettier. https://www.makeuseof.com/tag/programmer-onenote-coding-add-on/ 
Read more

Wasted about an hour of my life on something so trivial

-
Image
 Ok guys so i wasted about an hour trying to figure out how to bind to the "disabled" property in a textbox in angular. And after trying for a long time i realized that my code is not working as expected because , you have to use null instead of "false" to control it. Please refer to the stackoverflow answer below. Darn it ! https://stackoverflow.com/questions/42179150/how-to-disable-a-input-in-angular2/43765804
Read more

With Values

-
Image
 Using the clause WITH VALUES when you are adding a new column to an existing table using Microsoft SQL Server is a handy little trick. The use of WITH VALUES is when you want to add a default constraint to an existing table that allows null . Because when you add a default constraints that allows null it will NOT fill the existing rows with a value, this can effect your database logics ( if you have written some code depending on these values ).  Let me demonstrate this with some simple tables. Create a simple table CREATE TABLE Students( id int NOT NULL, name varchar(50) NULL ) Add some sample data insert into Student (id,name) values (1,'Cappadonna'), (2,'UGod') Add a new column to the table , with NOT NULL  clause ALTER TABLE Student ADD age int not null CONSTRAINT age_default DEFAULT 99 Now if you query your table you will see that the existing rows are being populated with your default value. This is because we added the constraint as NOT NULL.    No...
Read more