-4

There are two tables.

table 1

A   B      C
=================   
1  AAA   AA;BB
2  BBB   CC;DD
3  CCC   FF;G
4  DDD   E

table 2 (this table is expanded based upon columns C in above table which has ";" seperated values, these are expanded in terms of seperate rows)

A    B    C   is_valid
11   AAA  AA     0
12   AAA  BB     0
14   AAA  CC     0

Below table is formed by checking if the expansion is correct and is_valid flag is raised to 1. So checking needs to be done - Columns B should be exactly same in both the tables and part of column C present in table2 should be available with the same Column B value combination in table1.

Expected table 2 after update query

A    B    C   is_valid
11   AAA  AA     1
12   AAA  BB     1
14   AAA  CC     0
17   DDD  E      1

Need to get help on formation of the update query for updating the is_valid column in table2.

Andrew
  • 1,544
  • 1
  • 18
  • 36
Vinay Ranjan
  • 294
  • 3
  • 14

1 Answers1

1

To evaluate a like condition where the comparison is another column, you append the wildcard % to either end of the column and apply like as normal, I would do this in your where clause however, not the join, like this:

UPDATE t2
SET t2.isValid = 1
FROM table2 t2
JOIN Table1 t1
ON t1.B = t2.B
WHERE t1.C LIKE '%'+t2.C+'%'

Though you could have it as part of the join on like this:

UPDATE t2
SET t2.isValid = 1
FROM table2 t2
JOIN Table1 t1
ON t1.B = t2.B AND t1.C LIKE '%'+t2.C+'%'

UPDATE

you should use CONCAT('%',t2.C,'%') over '%'+t2.C+'%' as concat will work on more DBMS systems.

Andrew
  • 1,544
  • 1
  • 18
  • 36
  • Worth nothing that not all solutions work with all dbms, and you went a little tag happy there. MySQL, SQL-Server and Oracle all have different syntax. This would work on SQL-Server, but I make no guarantees for the other two. – Andrew Jan 31 '18 at 18:06
  • Don't post answer when you don't know which dbms the OP uses. This will work in SQL Server, but not the other two. – Eric Jan 31 '18 at 18:09
  • @Eric Apparently Vinay uses SQL-Server – Andrew Feb 01 '18 at 18:43