Mixing 3 sql queries in 1

Question

I have 3 tables as follows

Table1

Id Name
1   abcd
2   bcd
3   dabc

Table2

Id2 Name2
2   xyz
3   def
4   mno

Table3

Id  Id2  Value
1    4    1
2    3    1
3    4    1

Now,

From table1 : I have to select all Id where Name is %abc% From table2: I have to select Id2 where Name2 is "mno" From Table3: I have to change value to 0 from 1 where Id's value are from Table1 and Id2 is from Table2.

Table 1:

select Id from Table1 where Name like '%abc%'

Table2 :

select Id2 from Table2 where Name2 = "mno"

Table 3:

update Table3 set Value = 0 where Id in() and Id2=

But, I dont know how to make it 1 single query. Can anyone please guide me up ?

You should look into join queries"http://dev.mysql.com/doc/refman/5.7/en/join.html" — NcDreamy, Jan 13 '16 at 22:43
Nice Visual aid on joins here: http://blog.codinghorror.com/a-visual-explanation-of-sql-joins/ — xQbert, Jan 13 '16 at 22:44

score 2 · Answer 1 · edited May 23 '17 at 11:52

Refer to: prior stack article

You've not explained how T1 relates to T2, So I have assumed a cross join.

Whenever you have a record in T1 with name like '%abc%' (1,3) in your data.. and whenever you have a record in T2 with a name equal to 'mno' 4 then you want the value in table 3 to be 0

so the select we generate should produce 1,4 3,4

and when we inner join this back to table 3 it only selects

Id  Id2  Value
1    4    1
3    4    1

Now we generate an update based on this select as outlined in the link provided above...

UPDATE table3 
INNER JOIN (
  SSELECT t1.ID t1ID, t2.id t2ID
  FROM table1 t1
  CROSS JOIN table2
  WHERE t1.name like '%abc%'
    and t2.name like = 'mno') B
 on B.t1ID = t3.Id 
and B.t2ID = T3.ID2
SET value = 0

Giving us a result of

Id  Id2  Value
1    4    0
2    3    1
3    4    0

if we select * from table3

score 0 · Answer 2 · answered Jan 13 '16 at 22:55

0

update t3
set t3.Value = 0
from Table3 t3
inner join Table1 t1
  on t3.Id = t1.Id
inner join Table2 t2
  on t3.Id2 = t2.Id2
where t1.Name like '%abc%' and t2.Name2 = 'mno'

OR

update Table3
set value = 0
where Id in (select Id from Table1 where Name like '%abc%')
and Id2 in (select Id2 from Table2 where Name2 = 'mno')

answered Jan 13 '16 at 22:55

joordan831

720
5
6

Notice, try to avoid using IN subqueries as possible and use EXISTS instead. Ref: http://explainextended.com/2010/05/27/left-join-is-null-vs-not-in-vs-not-exists-nullable-columns/ Thank you – Atheer Mostafa Jan 13 '16 at 23:09
not really. read about subquery optimization by optimizer http://dev.mysql.com/doc/refman/5.7/en/subquery-optimization.html – joordan831 Jan 13 '16 at 23:43

score 0 · Answer 3 · answered Jan 13 '16 at 22:59

You should think about UPDATE ... WHERE EXISTS as follows:

update Table3 set Value = 0 
WHERE EXISTS (SELECT 1 FROM Table1 where Name LIKE '%abc%' AND  Table1.Id=Table3.Id )
AND EXISTS (SELECT 1 FROM Table2 where Name2 = "mno" AND Table2.Id2=Table3.Id2)

Mixing 3 sql queries in 1

3 Answers3