Newbie MySQL UPDATE problem

Classic Servoy
1

I have a SQL syntax problem that I have been trying to solve. Unfortunately my SQL is very weak, so I wondered whether anyone could give me some help? I am using MySQL v4.1.17 which supports sub queries.

I have two tables, consumers and sailings as below, the two tables should be joined by the match_key, and there is a one to many relationship, i.e. one consumer match_key will match one or more sailings match_key. The category field in the sailings table will have either a “C” or will be blank (not NULL).

I want to update the sailings field in each consumer record with a count of the category=C sailing records.

For example, the first record in the consumers table might have 30 matching records in the sailings table (using match_key to match_key), 15 of which will have a category of “C”, so the sailings field for this consumer should be updated with 15.

I just cannot seem to work out the SQL statement needed to carry out this update, if anyone could help I would be eternally grateful.

Thanks

consumers
cons_id
match_key
sailings

sailings
sail_id
match_key
category

2

something like this should work:

update consumers set sailings = (select count (*) from sailings where consumers.match_key = sailings.match_key)
3

Thanks for responding so quickly Patrick, I will give it a try and let you know how I got on.

4

Unfortunately, didn’t give the right result. I’m not looking to count the number of matching records, I’m looking to count the number of matching records on the sailings table that have a category=C.

How do I include this extra criteria.

5

sorry, I didn’t read the whole thing well enough… Try

update consumers set sailings = (select count (*) from sailings where consumers.match_key = sailings.match_key and sailings.category = 'C')
6

I’m getting an incorrect syntax error!

Sorry to be a pain.

7

what error are you getting and where (if you break the statement in lines)? This statement should work and deliver the number of sailings having the category “C” just fine…

8

I have taken the space out after COUNT and it now is running. Will let you know result.

Thanks

9

What do you mean by “now is running”? How many records do you have? Need a faster database? :wink:

10

Have 1.3 million records on the consumer table and 2.6 million records on the sailings table.

Have to admit that I didn’t start it until recently, has been running for 27mins so far!

11

I am using Navicat to submit the Update, and the server monitor is telling me that it is sending data. Does this mean that it’s done it, but its now trying to send me all the data!!!

12

it’s not very uncommon that an update statement takes this long.(or even much longer)
(taking into consideration the nr of records and the kind of update you’re doing)
Secondly Servoy doesn’t broadcasts these updates automatically to the connected clients.

Your database tool should give you a message when query is ready.
(don’t forget to commit)

TIP:
when doing very large updates, first do an update on a small set of records (preferably a test database) and check the query time, giving you an estimate for the complete set to be updated.
Also good for verifying that your update is actually doing exactly what you want. ;)