SQL insert into existing table based on condition

Question

I have an existing table vault in my database, with some data in it. Now I need to add another column to it. It's a boolean is_master telling whether each vault is the "master" vault or not.

The condition whether the vault is master or not can be calculated based on the current values.

IF vault.name = "Master" --> is_master = true
ELSE --> is_master = false

I need an SQL script that will run through all my existing vaults in the database and calculate the new is_master column based on the name column.

I don't know exactly how to write such condition in SQL, it should probably be something like this:

ALTER TABLE vault
ADD COLUMN is_master BOOLEAN NOT NULL;

INSERT INTO vault (id, created_at, updated_at, company_name, country, is_master)
SELECT id, created_at, updated_at, company_name, country, true // false if not "Master"
FROM vault
WHERE vault.name = 'Master';

Could someone help me out with the query?

You're looking for an UPDATE query, not an INSERT.

Bergi
– Bergi

2019-11-05 23:44:24 +00:00
Commented Nov 5, 2019 at 23:44 — Bergi
– Bergi, Commented Nov 5, 2019 at 23:44

Gordon Linoff · Accepted Answer · 2019-11-06 13:34:54Z

4

You can assign a boolean:

ALTER TABLE vault ADD COLUMN is_master BOOLEAN NOT NULL;

UPDATE value
    SET is_master = (name = 'Master');

EDIT:

To handle NULL values for master:

UPDATE value
    SET is_master = (name IS NOT DISTINCT FROM 'Master');

edited Nov 6, 2019 at 13:34

answered Nov 5, 2019 at 23:36

Gordon Linoff

1.3m62 gold badges705 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Whizzil Over a year ago

I seem to be getting some null constraint errors. ERROR: column \"is_master\" contains null values

Collectives™ on Stack Overflow

SQL insert into existing table based on condition

1 Answer 1

1 Comment

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Related