Check table column and insert if value exists-CodePudding

I have two tables that has a column value in both but some are missing. I want to compare table 2 and insert a 1 if it is present, and 0 if it is not present in table 1. I am a newbie so , would appreciate some help on this.

Table 1:

custid  name   lastname  acc_exists
123456  eric   john
54658   david  peter

Table 2:

custid  name   order
54658   david  kl6545
65282   matt   pl5865

SQL:

INSERT INTO t1(acc_exists)  
SELECT UPPER(t1.custid)
FROM dbo.customer_accounts T1
LEFT OUTER JOIN dbo.customer_details t2
ON UPPER(t1.custid) = UPPER(t2.custid)

CodePudding user response：

The INSERT statement adds rows to a table. If you need to update column values of existing rows you need an UPDATE statement. If the "customer_accounts" table has no "acc_exists" field, you need to first alter the table to add that field:

ALTER TABLE customer_accounts ADD acc_exists2 INT;

Then you update values for this field by left joining between the two tables and assigning:

0, when the "customer_details" table has NULL value
1, otherwise

Given that you want to ignore case sensitivity in you join, you can cast the collation in the ON clause using collate Latin1_General_CI_AS:

UPDATE customer_accounts
SET acc_exists = CASE WHEN d.custid IS NULL THEN 0 ELSE 1 END
FROM      customer_accounts a 
LEFT JOIN customer_details d
       ON a.custid collate Latin1_General_CI_AS = d.custid collate Latin1_General_CI_AS;

Check the demo here.

Note: I'm assuming that the field "custid" uniquely identifies the single customer.