CODEWITHSUNDEEP
sqlsql-serverif-statementsql-update
Saleigh
Saleigh

Reputation: 23

how do I update SQL columns with true or false based data in another column on the same table?

Please see attached image

enter image description here

I would like to update Supplier & Agent Columns with either true or false, based on the info in the Supplier_Agent column. How would i do this?

Also, some records do not have any info in the Supplier_Agent column so for them, both Supplier & Agent Columns would be FALSE.

Upvotes: 1

Views: 1885

Answers (3)

Bob Dubke
Bob Dubke

Reputation: 66

You should you the case when function, so you can set the value checking a specific condition.

update SNPD
set SNPD.supplier= case when SNPD.Supplier_Agent='Supplier' then 1 else 0 
end,
SNPD.Agent= case when SNPD.Supplier_Agent='Agent' then 1 else 0 end 
FROM SuppliersNPD as SNPD

I also recommend you to use enumeration instead of 'supplier' and 'agent' to identify the type of supplier. For example, you could have created a column called Type and use code 100 for suppliers and 200 for agents. This seems more complicated but makes the table easier to be maintained and it is often used in ERP software.

Upvotes: 0

Fahmi
Fahmi

Reputation: 37473

Use case when

update dbo.SuppliersNPD
set supplier= case when supplier_aggent='supplier' then 1 else 0 end,
agent= case when supplier_aggent='agent' then 1 else 0 end

Upvotes: 2

Zaynul Abadin Tuhin
Zaynul Abadin Tuhin

Reputation: 31993

make your agent and supplier column in bit datatype then do update

 update t
  set supplier=case when Supplier_Agent='Supplier'  then 1 else 0 end,
   agent =case when Supplier_Agent='Agent'  then 1 else 0 end

In sql server there is no boolean data type so 0 means false and 1 means true

Upvotes: 3

Related Questions