CODEWITHSUNDEEP
mysql
Pierre S.
Pierre S.

Reputation: 21

Updating a Table by counting results from another table

I am trying to update the table T1 by counting the results of a SQL Query from another table T2.

This is the query I came up with but it does not seem to work:

UPDATE T1 set Stock = (select count (ID_Item)

FROM T2, T1

WHERE   T2.ID_Product=T1.ParentSKU AND
   T2.Name='' AND
   T2.Returned=''

I am getting 0 for the result even though I know it should not be. Any help would be great.

Upvotes: 0

Views: 24

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270021

The correct syntax is:

UPDATE T1
    SET Stock = (SELECT count(ID_Item)
                 FROM T2
                 WHERE T2.ID_Product = T1.ParentSKU AND
                       T2.Name = '' AND
                       T2.Returned = ''
                );

You should not repeat the reference to T1 in the correlated subquery. You need a connection between the subquery and the table being updated.

Upvotes: 1

Related Questions