How to run two SQL queries at the same time?

  • (2 Pages)
  • +
  • 1
  • 2

15 Replies - 944 Views - Last Post: 07 April 2015 - 02:08 AM

#1 aripalx  Icon User is offline

  • New D.I.C Head

Reputation: -2
  • View blog
  • Posts: 32
  • Joined: 30-March 15

How to run two SQL queries at the same time?

Posted 02 April 2015 - 09:40 AM

Regarding the attached file "test.png".

My question is, I wish to find the total average from the column "salery", and then change that result with everyone that has "Pettersen" as LastName?

Here is the workflow:
Step 1: Find average from salery.
Step 2: Change "Pettersens" salery with the average result.

Here is my approach, but it did not work, I got error: "#1111 - Invalid use of group function".
UPDATE products
SET Salery = AVG(Salery)
WHERE LastName='Pettersen';



Is This A Good Question/Topic? 0
  • +

Replies To: How to run two SQL queries at the same time?

#2 modi123_1  Icon User is online

  • Suitor #2
  • member icon



Reputation: 13391
  • View blog
  • Posts: 53,443
  • Joined: 12-June 08

Re: How to run two SQL queries at the same time?

Posted 02 April 2015 - 09:47 AM

AVG is an aggregate function. It needs a group by.
https://dev.mysql.co...-functions.html


Why wouldn't you simplify things and get the salary average in a variable and use that in your UPDATE?
Was This Post Helpful? 0
  • +
  • -

#3 aripalx  Icon User is offline

  • New D.I.C Head

Reputation: -2
  • View blog
  • Posts: 32
  • Joined: 30-March 15

Re: How to run two SQL queries at the same time?

Posted 02 April 2015 - 10:01 AM

View Postmodi123_1, on 02 April 2015 - 09:47 AM, said:

AVG is an aggregate function. It needs a group by.
https://dev.mysql.co...-functions.html


Why wouldn't you simplify things and get the salary average in a variable and use that in your UPDATE?


I just want to know if it is possible to do two things instead of dividing it?
Was This Post Helpful? 0
  • +
  • -

#4 modi123_1  Icon User is online

  • Suitor #2
  • member icon



Reputation: 13391
  • View blog
  • Posts: 53,443
  • Joined: 12-June 08

Re: How to run two SQL queries at the same time?

Posted 02 April 2015 - 10:15 AM

Yes. You can set the value to an outcome of a query.

Example:

 cre ate table #foo (s varchar(10), x int)

 inse rt into #foo(s, x)values ('abc', 3)
 ins ert into #foo(s, x)values ('def', 4)

 up date #foo
 set x = (select x * 3 from #foo where s = 'abc')
 where s = 'abc'

 select *
 from #foo

 d rop table #foo 

Was This Post Helpful? 0
  • +
  • -

#5 baavgai  Icon User is offline

  • Dreaming Coder
  • member icon


Reputation: 6966
  • View blog
  • Posts: 14,572
  • Joined: 16-October 07

Re: How to run two SQL queries at the same time?

Posted 02 April 2015 - 12:15 PM

Tis always good to use the database in question. Because, well, specific engines have specific quirks. ( As we shall see. )

I started out with a temp table too...
mysql> use test;
Database changed
mysql> create temporary table foo(s varchar(10), x int);
Query OK, 0 rows affected (0.01 sec)

mysql> insert into foo values ('abc', 3), ('def', 4);
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select * from foo;
+------+------+
| s    | x    |
+------+------+
| abc  |    3 |
| def  |    4 |
+------+------+
2 rows in set (0.00 sec)

mysql> update foo set x = x * 3 where s='abc';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from foo;
+------+------+
| s    | x    |
+------+------+
| abc  |    9 |
| def  |    4 |
+------+------+
2 rows in set (0.00 sec)

mysql> select avg(x) from foo;
+--------+
| avg(x) |
+--------+
| 6.5000 |
+--------+
1 row in set (0.00 sec)

mysql> update foo set x = (select avg(x) from foo) where s='abc';
ERROR 1137 (HY000): Can't reopen table: 'foo'
mysql> select *
    -> from foo a
    -> cross join (select avg(x) from foo) b;
ERROR 1137 (HY000): Can't reopen table: 'a'



Huh? Time to go another route:
mysql> select * from foo a full join (select avg(x) from foo) b;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'full join (select avg(x) from foo) b' at line 1
mysql> 



Man, that just ain't right. Research finds that temp tables are a little odd in mysql. http://dev.mysql.com...e-problems.html

Right, try again:
mysql> create table foo(s varchar(10), x int);
Query OK, 0 rows affected (0.03 sec)

mysql> insert into foo values ('abc', 12), ('def', 4);
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select * from foo;
+------+------+
| s    | x    |
+------+------+
| abc  |   12 |
| def  |    4 |
+------+------+
2 rows in set (0.00 sec)

mysql> select avg(x) as n from foo;
+--------+
| n      |
+--------+
| 8.0000 |
+--------+
1 row in set (0.00 sec)

mysql> update foo set x = (select avg(x) from foo) where s='abc';
ERROR 1093 (HY000): You can't specify target table 'foo' for update in FROM clause



Hmm, guess the easy way still doesn't work. How about a join:
mysql> select * from foo a cross join (select avg(x) from foo) b;
+------+------+--------+
| s    | x    | avg(x) |
+------+------+--------+
| abc  |   12 | 8.0000 |
| def  |    4 | 8.0000 |
+------+------+--------+
2 rows in set (0.01 sec)



That looks promising. Note, MySql update join syntax is vendor specific. The above is vendor generic, if the vendor wasn't so pedantic. Let's try:
mysql> update foo a cross join (select avg(x) as n from foo) b set a.x = b.n where a.s='abc';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from foo;
+------+------+
| s    | x    |
+------+------+
| abc  |    8 |
| def  |    4 |
+------+------+
2 rows in set (0.00 sec)

mysql> 



Success.
Was This Post Helpful? 4
  • +
  • -

#6 aripalx  Icon User is offline

  • New D.I.C Head

Reputation: -2
  • View blog
  • Posts: 32
  • Joined: 30-March 15

Re: How to run two SQL queries at the same time?

Posted 02 April 2015 - 01:46 PM

#1093 - You can't specify target table 'Persons' for update in FROM clause

Can someone make this work? I just want to change Pettersen's salery with the average salery result..

UPDATE Persons 
SET Salery = (SELECT AVG(Salery) FROM Persons)
WHERE LastName = 'Pettersen'


Was This Post Helpful? -1
  • +
  • -

#7 modi123_1  Icon User is online

  • Suitor #2
  • member icon



Reputation: 13391
  • View blog
  • Posts: 53,443
  • Joined: 12-June 08

Re: How to run two SQL queries at the same time?

Posted 02 April 2015 - 01:49 PM

Please remember teh rules about asking folks to do your work for you.
Was This Post Helpful? 0
  • +
  • -

#8 aripalx  Icon User is offline

  • New D.I.C Head

Reputation: -2
  • View blog
  • Posts: 32
  • Joined: 30-March 15

Re: How to run two SQL queries at the same time?

Posted 03 April 2015 - 08:57 AM

View Postmodi123_1, on 02 April 2015 - 01:49 PM, said:

Please remember teh rules about asking folks to do your work for you.


Can you please explain teh rules?
Was This Post Helpful? -1
  • +
  • -

#9 baavgai  Icon User is offline

  • Dreaming Coder
  • member icon


Reputation: 6966
  • View blog
  • Posts: 14,572
  • Joined: 16-October 07

Re: How to run two SQL queries at the same time?

Posted 03 April 2015 - 12:52 PM

The rule is we won't do your work for you. That said... I gave you the answer, man! If you can't be even be bothered to replace my names with yours, then why should anyone feel the need to do it for you? A little effort on your part goes a long way to eliciting effort on ours. And, well, zero effort gets you nothing.
Was This Post Helpful? 0
  • +
  • -

#10 aripalx  Icon User is offline

  • New D.I.C Head

Reputation: -2
  • View blog
  • Posts: 32
  • Joined: 30-March 15

Re: How to run two SQL queries at the same time?

Posted 05 April 2015 - 04:56 AM

I appreciate the answer and help I get. But if I do not understand the answer then I do not understand it, as simple as that. What I am looking for is why this do not work? Or in other words, how can I make this work because "AVG(Salery)" does work. The idea here is to replace a specific field inside column "Salery" with the average result "AVG(Salery)"..

UPDATE products
SET Salery = AVG(Salery)
WHERE LastName='Pettersen';


Was This Post Helpful? 0
  • +
  • -

#11 aripalx  Icon User is offline

  • New D.I.C Head

Reputation: -2
  • View blog
  • Posts: 32
  • Joined: 30-March 15

Re: How to run two SQL queries at the same time?

Posted 05 April 2015 - 05:03 AM

How can I make this work because "AVG(Salery)" does not work. The idea here is to replace a specific field inside column "Salery" with the average result "AVG(Salery)"..

UPDATE products
SET Salery = AVG(Salery)
WHERE LastName='Pettersen';



For instance I want find the total average from people's Salery, then replace that average result with the person who has Pettersen's as last name.
Was This Post Helpful? 0
  • +
  • -

#12 modi123_1  Icon User is online

  • Suitor #2
  • member icon



Reputation: 13391
  • View blog
  • Posts: 53,443
  • Joined: 12-June 08

Re: How to run two SQL queries at the same time?

Posted 05 April 2015 - 07:06 AM

Two things.. first this sort of post is not a 'site support' question. Second, please do not create duplicate topics.

I merged it in with your existing thread.
Was This Post Helpful? 0
  • +
  • -

#13 baavgai  Icon User is offline

  • Dreaming Coder
  • member icon


Reputation: 6966
  • View blog
  • Posts: 14,572
  • Joined: 16-October 07

Re: How to run two SQL queries at the same time?

Posted 05 April 2015 - 07:54 AM

View Postaripalx, on 05 April 2015 - 07:56 AM, said:

But if I do not understand the answer then I do not understand it


Fair enough. I gave you this and showed that it didn't work:
mysql> update foo
    -> set x = (select avg(x) from foo)
    -> where s='abc';
ERROR 1093 (HY000): You can't specify target table 'foo' for update in FROM clause



I then showed you this and demonstrated that it did work:
mysql> update foo a
    -> cross join (select avg(x) as n from foo) b
    -> set a.x = b.n
    -> where a.s='abc';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0



If you can't take your given example of
UPDATE Persons
SET Salery = (SELECT AVG(Salery) FROM Persons)
WHERE LastName = 'Pettersen'



and extrapolate it into the second form. Essentially all you are being asked to do is change names to fit what you already have. If this is beyond your understanding, then I'm afraid SQL and programming in general is not a viable path for you. I am sorry.


View Postaripalx, on 05 April 2015 - 07:56 AM, said:

What I am looking for is why this do not work?


Again, this was already answered. In one case, SET Salery = AVG(Salery), it won't work because AVG is an aggregate function that requires a result set and not a scalar value. In the case of SET Salery = (SELECT AVG(Salery) FROM Persons), this is a limitation specific to MySql. The nature of the limitation is that you can't use a subquery result from a table that you're trying to assign update. Essentially, the DB engine has an issue with the same table you're trying to update already being busy and therefor unavailable to update.
Was This Post Helpful? 0
  • +
  • -

#14 aripalx  Icon User is offline

  • New D.I.C Head

Reputation: -2
  • View blog
  • Posts: 32
  • Joined: 30-March 15

Re: How to run two SQL queries at the same time?

Posted 05 April 2015 - 10:04 AM

I shall be trying this and see if it works, thank you for giving your time and help :)

Quote

"If this is beyond your understanding, then I'm afraid SQL and programming in general is not a viable path for you. I am sorry."


Btw: I will graduate with my bachelor degree as computer engineer in embedded systems in June. So I think "programming in general" is a viable path for me ;)
Was This Post Helpful? 0
  • +
  • -

#15 astonecipher  Icon User is offline

  • Too busy for this
  • member icon

Reputation: 2329
  • View blog
  • Posts: 9,356
  • Joined: 03-December 12

Re: How to run two SQL queries at the same time?

Posted 05 April 2015 - 12:20 PM

View Postaripalx, on 05 April 2015 - 01:04 PM, said:

Btw: I will graduate with my bachelor degree as computer engineer in embedded systems in June. So I think "programming in general" is a viable path for me ;)/>

All defensiveness aside, that doesn't mean anything. All having a degree means, regardless of how you feel, is you were in school and met the minimum requirements for them. Programming in the real world is a bit different then in the academic world.

If you need proof of that wait until you experience it. There are untold numbers of people with degrees that are unemployed, because they believe that a degree means anything. A degree may land you an interview, nothing more.
Was This Post Helpful? 1
  • +
  • -

  • (2 Pages)
  • +
  • 1
  • 2