Incrementing values more than once on mysql on a single UPDATE

Question

I've been looking for an answer to my problem but to no avail. The problem is as follows, when I try to update a value more than once on a single UPDATE statement in SQL it will always update once. Is as if the UPDATE statement is working over a copy of my table and always overwriting the value on the original table, hence the resulting table only has the value incremented by 1 and not by the number of times the value was set with value=value+1.

Here's an example:

UPDATE Home, Person
SET Home.NumberOfChilds=Home.NumberOfChilds+1
WHERE Home.State= Person.State
AND Home.ZoneCode = Person.ZoneCode 
AND Home.Address = Person.Address
AND Person.IsChild = true;

In this case, if a home has 3 childs the resulting amount of children would be 1 on that home, when I need it to be 3. Thanks in advance.

That's just an example because the JOIN is much longer and my tables and attributes are not in English. State, ZoneCode and Adress would be the PK of Home, and State, ZoneCode, Adress and NumberOfPerson would be the PK of Person, and the first three of those would be a FK referencing Home. NumberOfChilds is a Decimal(4,0). Hope it helps, thanks for your interest. — edu
– edu, Commented Jun 26, 2013 at 16:31

Kickstart · Accepted Answer · 2013-06-26 16:32:38Z

1

Possibly do the count in a subselect joined against your main table:-

UPDATE Home
INNER JOIN (SELECT State, ZoneCode, Address, COUNT(*) AS PersonCount FROM Person WHERE IsChild = true GROUP BY  State, ZoneCode, Address) Sub1
ON Home.State= Sub1.State
AND Home.ZoneCode = Sub1.ZoneCode 
AND Home.Address = Sub1.Address
SET Home.NumberOfChilds = Home.NumberOfChilds + Sub1.PersonCount

answered Jun 26, 2013 at 16:32

Kickstart

21.5k2 gold badges26 silver badges33 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

JSR · Accepted Answer · 2013-06-26 16:31:59Z

0

This works with Sql Server 2008R2, I don't have access to to a mysql system to test at the moment.

UPDATE H
SET H.NumberOfChilds = ( 
    SELECT COUNT(*) FROM Person AS P
    WHERE H.State = P.State
    AND H.ZoneCode = P.ZoneCode
    AND H.Address = P.Address
    AND P.IsChild = 1 )                       
FROM Home AS H

I don't think you will be able to get multiple increments of 1 from a single statement, you will need to use some form of count instead.

answered Jun 26, 2013 at 16:31

JSR

6,4162 gold badges22 silver badges24 bronze badges

3 Comments

edu Over a year ago

Thanks for the answer, I forgot to mention that my tables have millions of rows and that solution takes too much time to complete, but I'm sure it would work

Tony Hopkinson Over a year ago

My answer will probably exhibit the same weakness then, though indexes might help as might a surrogate key e.g Person.HomeId. Other than that, you might have to look at building the output via a trigger or stored proc

Kickstart Over a year ago

This relies on a correlated subselect which is likely to perform badly. Both my solution and the very similar one by @TonyHopkinson shouldn't suffer in this way.

Tony Hopkinson · Accepted Answer · 2013-06-26 16:35:32Z

0

Something like

Update h
Set h.NumberOfChilds = c.ChildCount
From Home h
inner join (Select ZoneCode, Address, Count(*) as ChildCount 
            From Person Where IsChild = true Group By ZoneCode,Address) c
On c.ZoneCode = h.ZoneCode and c.Address = h.Address

maybe.

All you are doing is incrementing child count for all homes with at lest one matching child each time you run the query.

answered Jun 26, 2013 at 16:35

Tony Hopkinson

20.4k3 gold badges35 silver badges40 bronze badges

1 Comment

edu Over a year ago

Thanks for the answer, it works that way like I told Kickstart.

Collectives™ on Stack Overflow

Incrementing values more than once on mysql on a single UPDATE

3 Answers 3

Comments

3 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

3 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related