By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
440,828 Members | 801 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 440,828 IT Pros & Developers. It's quick & easy.

update column in table A with the count of another table

P: n/a
Having my SQL-background in Oracle I'm looking for the MySQL equivalent for:

update tableA
set column1=(select count(*) from tableB where tableA.key= tableB.key)

cannot find anything similar in the manual, but probably overlooking
something.

TIA

Peter
Jul 23 '05 #1
Share this Question
Share on Google+
5 Replies


P: n/a
Peter van Rijn wrote:
Having my SQL-background in Oracle I'm looking for the MySQL equivalent for:

update tableA
set column1=(select count(*) from tableB where tableA.key= tableB.key)

cannot find anything similar in the manual, but probably overlooking
something.


Did you try it? It seems to work in a brief test I did in my test database.

Regards,
Bill K.
Jul 23 '05 #2

P: n/a
Good morning Bill,

Thanks for your response.

Of course I tried. Could this be version dependend? What version is your
test database?

regards,
Peter

"Bill Karwin" <bi**@karwin.com> schreef in bericht
news:d3********@enews1.newsguy.com...
Peter van Rijn wrote:
Having my SQL-background in Oracle I'm looking for the MySQL equivalent
for:

update tableA
set column1=(select count(*) from tableB where tableA.key= tableB.key)

cannot find anything similar in the manual, but probably overlooking
something.


Did you try it? It seems to work in a brief test I did in my test
database.

Regards,
Bill K.

Jul 23 '05 #3

P: n/a
Peter van Rijn wrote:
Of course I tried. Could this be version dependend? What version is your
test database?


Subqueries are supported from version 4.1, so if you have older version,
I'm sure it won't work.
Jul 23 '05 #4

P: n/a
Peter van Rijn wrote:
Good morning Bill,

Thanks for your response.

Of course I tried. Could this be version dependend? What version is your
test database?


If you are using MySQL 4.0 or earlier, the query you used will not work
because subqueries are not supported in those releases. You need to use
MySQL 4.1.x.

Regards,
Bill K.
Jul 23 '05 #5

P: n/a
Bill and Aggro,

Thanks, it's the version that was bothering me.

regards

Peter
"Bill Karwin" <bi**@karwin.com> schreef in bericht
news:d3*********@enews3.newsguy.com...
Peter van Rijn wrote:
Good morning Bill,

Thanks for your response.

Of course I tried. Could this be version dependend? What version is your
test database?


If you are using MySQL 4.0 or earlier, the query you used will not work
because subqueries are not supported in those releases. You need to use
MySQL 4.1.x.

Regards,
Bill K.

Jul 23 '05 #6

This discussion thread is closed

Replies have been disabled for this discussion.