0

My actual (and broken) query is this:

UPDATE t_1 SET b=(SELECT b FROM t_1 WHERE a=1)  WHERE b=1

How can I do it using a inner join?

Alexander
  • 13
  • 1
  • 1
  • 4
  • 2
    I totally do not understand what you want. Can you add a example of how your rows should look like before and after? – Dorus Jun 19 '15 at 14:49
  • `and broken`, why is it broken? – Giorgi Nakeuri Jun 19 '15 at 14:56
  • @Dorus a=1 and b=1 must have always the same number, it's very hard to explain why. It's broken because you can't do a subquery of the same table... – Alexander Jun 19 '15 at 15:07
  • Something like `UPDATE t_1 SET b=(SELECT TOP 1 t_2.b from t_1 as t_2 WHERE t_2.a=t_1.b)`? (That probably still wont work, but it gives the idea) – Dorus Jun 19 '15 at 15:13
  • @Dorus look the Tim3880's answer... – Alexander Jun 19 '15 at 15:14
  • I suggest you stick with what works and worry about performance when it's actually too slow, not when you think it might be slow. – Dorus Jun 19 '15 at 15:16
  • @Dorus thanks, but I am absolutely sure that I can do it with inner join, but I don't know how... – Alexander Jun 19 '15 at 15:19
  • 1. You should have shown us the working version and asked one with a inner join. 2. I like to see a benchmark between them. – Dorus Jun 19 '15 at 15:59

2 Answers2

5

You can cheat MySQL on this:

UPDATE t_1 
SET b=(SELECT b FROM (select * from t_1) t WHERE a=1)  
WHERE b=1

A join version:

update t_1
join (select * from t_1) t
on t.a = 1 and t_1.b=1
set t_1.b= t.b;

Where is your source proving that all sub queries are slower than joins?

Tim3880
  • 2,563
  • 1
  • 11
  • 14
  • Thanks, it's true, but I want to use a inner join because they are faster than subqueries... – Alexander Jun 19 '15 at 15:13
  • 1
    @Alexander Not true - _some_ subqueries may be slower, but it depends entirely on how the compiler develops the plan. – D Stanley Jun 19 '15 at 15:27
0

Well, you could do it this way:

UPDATE t_1 
SET t1.b=t2.b
FROM T_1 t1
INNER JOIN T_1 t2
  ON t2.a=1
WHERE b=1

So if that's more readable for you then go for it.

D Stanley
  • 149,601
  • 11
  • 178
  • 240