-1

I have this table:

enter image description here

Now if I count rows in column id_racuna that are not NULL using: 

SELECT COUNT (id_racuna) FROM racuni WHERE id_racuna IS NOT NULL;

I get:

enter image description here

So if I use this value in an update like this: 

UPDATE racuni AS r1 JOIN racuni AS r2 ON r1.id_interesa = r2.id_interesa
SET r1.id_racuna = (SELECT COUNT (r2.id_racuna) FROM r2 WHERE r2.id_racuna IS NOT NULL) +1,
    r1.poslano = curdate()
WHERE r1.id_interesa = 8;

I would expect to get:

enter image description here

but I get an error:

Table r2 doesn't exist!

I tried to trick MySQL using JOIN to think that r1 and r2 are two different tables, but it doesn't work. I have seen this kind of trick here where it worked... What am I missing?

UPDATE:

I need to use JOIN because if I wrote just: 

UPDATE racuni
SET id_racuna = (SELECT COUNT (id_racuna) FROM racuni WHERE id_racuna IS NOT NULL) +1,
    poslano = curdate()
WHERE id_interesa = 8;

I would get error:

Table 'racuni' is specified twice, both as a target for 'UPDATE' and as a separate source for data

71GA
  • 1,132
  • 6
  • 36
  • 69
  • I mean you're referring to two tables as r2. Maybe try without that to see where MySQL is unhappy? – 0x11 Sep 22 '17 at 19:43
  • If I leave it out, I would get error: Table is specified twice, both as a target for 'UPDATE' and as a separate source for data. – 71GA Sep 22 '17 at 19:43
  • Oh I see. Well why not set a variable equal to the value of the count, then run your straightforward update query below? – 0x11 Sep 22 '17 at 19:45
  • Below is related to MySQL update join syntax: https://stackoverflow.com/questions/1262786/mysql-update-query-based-on-select-query – Greg Sep 22 '17 at 19:48
  • based on https://stackoverflow.com/questions/4429319/you-cant-specify-target-table-for-update-in-from-clause the follwoing should also work: `UPDATE racuni SET id_racuna = (SELECT * FROM (SELECT COUNT(id_racuna) FROM racuni) as t);` – Roland Starke Sep 22 '17 at 19:57

1 Answers1

1

Try with a cross join and wrap your sub query in it

UPDATE racuni AS r1 
CROSS JOIN (SELECT COUNT (id_racuna) id_racunacount 
    FROM racuni 
    WHERE id_racuna IS NOT NULL) a
SET r1.id_racuna = a.id_racunacount + 1,
    r1.poslano = curdate()
WHERE r1.id_interesa = 8;
M Khalid Junaid
  • 63,861
  • 10
  • 90
  • 118
  • This works! Could you please explain why my solution didn't work and why your works? – 71GA Sep 22 '17 at 19:52
  • Because you are using the same table directly in `SET` clause while in mine you can see i calculated count in sub select and used a cross join to make a separate data set and gave an alias as `a` this way you can do an update on same table by doing selection from same table – M Khalid Junaid Sep 22 '17 at 19:55
  • Wouldn't usage of `JOIN` instead of `CROSS JOIN` execute faster? – 71GA Sep 22 '17 at 20:23
  • 1
    @71GA there is no `ON` clause if you see in query to get some benefit of index, so no i can't say/suggest anything about the execution of query – M Khalid Junaid Sep 22 '17 at 20:25
  • So is there a way to make this faster by not using a cross join? When table gets large it might execute slow. – 71GA Sep 22 '17 at 20:34
  • 1
    An index on `id_racuna` would be good because there is a where clause on this column so filtering of nulls will be faster – M Khalid Junaid Sep 22 '17 at 20:36