CODEWITHSUNDEEP
mysqlsqlstored-procedures
A.Grandt
A.Grandt

Reputation: 2252

Update table inside stored procedure updates all rows

I have the following simple three lines at the end of a stored procedure:

UPDATE `imthreadmember`
   SET `lastUpdate`=CURRENT_TIMESTAMP
 WHERE `threadId` = threadId;

However running this inside the proc, updates ALL rows in imthreadmember. Running the same three lines in the console, updates only the ones with that threadId. Inserting a select threadId; just before this statement returns the correct id.

I'm at my its end to figure out how to solve this.

The rows in imthreadmember were created earlier in the same proc, but as CURRENT_TIMESTAMP can only be used as default once per table I have to update this column separately, the default goes to the created column.

Upvotes: 1

Views: 1708

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269623

The name of your variable is the same as the name of the column. By MySQL's scoping rules, the column is being compared to itself.

It is a good habit to prepend all variables used in stored procedures with something like v_. So the query should look like:

UPDATE `imthreadmember`
   SET `lastUpdate`=CURRENT_TIMESTAMP
 WHERE `threadId` = v_threadId;

And, as a corollary, to never name a column starting with v_.

Upvotes: 5

Related Questions