CODEWITHSUNDEEP
mysqlsqlsubquery
James Oravec
James Oravec

Reputation: 20391

MySQL Update Value from SubQuery

I'm using MySQL and am running a query, which I think should work, but apparently I'm missing something.

0 records get updated when I run:

UPDATE `client` 
  SET StatementTermsID = (SELECT StatementTermsID FROM statementterms WHERE TermsDescription = 'NET 15') 
  WHERE `client`.StatementNote LIKE '%Net 15%';

If I run the subquery by itself, I get the record id as expected. If I change the subquery to be a static value, then 2000 plus records get updated. Any idea on what I'm missing?

Upvotes: 0

Views: 69

Answers (2)

VJ Hil
VJ Hil

Reputation: 904

i Think you should use a join. I can see that we cannot use StatementTermsID because thats what you are trying to update.So for sure it won`t be there in the former table. If you have any ids to join Use that as well.

UPDATE client
SET StatementTermsID =statementterms.StatementTermsID 
FROM client
INNER JOIN statementterms 
   WHERE statementterms.TermsDescription = 'NET 15' AND 
   client.StatementNote LIKE '%Net 15%';

Or else Try this syntax

UPDATE client , statementterms 
   SET client.StatementTermsID = statementterms .StatementTermsID 
 WHERE statementterms.TermsDescription = 'NET 15' AND 
       client.StatementNote LIKE '%Net 15%';

Upvotes: 1

Fabricator
Fabricator

Reputation: 12772

Here's a different syntax:

UPDATE `client` a,
(SELECT StatementTermsID FROM statementterms WHERE TermsDescription = 'NET 15') b
SET a.StatementTermsID = b.StatementTermsID
WHERE a.StatementNote LIKE '%Net 15%';

Upvotes: 1

Related Questions