Conditional where clause in Tsql

Question

question

Ajeesh asked Dec 21, '19

Conditional where clause in Tsql

I have 3 tables

Table1 with columns emp_id, code_1, code_2
Table 2 code_1, code_2
Table3 emp_id, emp_ind

Table2 is a lookup table. I need to update Table3 checking code_1 and code_2 of Table1 present in table2

If code_2 in table2 is null check if code_1 in table 1 and 2 match , if yes update emp_ind in Table3 as Y
If code_1 in table2 is null check if code_2 in table 1 and 2 match , if yes update emp_ind in Table3 as Y
If code_1 and code_2 both are valued in table 2, check if both code1 and code2 in Table1 match with that of table2 , if yes update emp_ind in Table3.

Is there a way to doi it simply instead of checking

Update table_3 t3
Set t3.emp_ind = 'Y'
where exists 
(Select 1 from table1 t1
Inner Join table2 t2 
on t1.code_1 = t2.code_1
Where t2.code_2 is null
And t2.emp_id= t3.emp_id
Union
Select 1 from table1 t1
Inner Join table2 t2 
on t1.code_2 = t2.code_2
Where t2.code_1 is null
And t2.emp_id= t3.emp_id
Union
Select 1 from table1 t1
Inner Join table2 t2 
on t1.code_1 = t2.code_1
And t1.code_2 = t2.code_2
Where t2.emp_id= t3.emp_id
)

tsql

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Answer 1 · 2020-01-07T15:16:48Z

Jon Crawford answered Jan 7, '20

well, if the only reason to go to table2 is to say "the code is present in the table", then just inner join to table2. It will only include records that have codes in that table, and your join from 1 to 3 will be on emp_id. You could do it in several steps to catch all the code positions you wanted.

Update table_3 Set emp_ind ='Y'

FROM table_3 t3

JOIN table_2 t2 ON t3.code_1 = t2.code_1

JOIN table_1 t1 ON t3.emp_id = t1.emp_id;

--repeat for code_2

UPDATE table_3 SET emp_ind = 'Y'

FROM table_3 t3

JOIN table_2 t2 ON t3.code_2 = t2.code_2

JOIN table_1 t1 ON t3.emp_id = t1.emp_id;

Also, try not to use an alias as the target of an UPDATE (or DELETE). Why? Well, because Jeff Moden said so. I don't remember and can't find the reason, something mumblety quirky update maybe. In any case, makes it clearer which table is being updated.

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

question