MySql variable stored with select count statement always returns 1

Question

Hello in a test database i have only one row in the table customer.I have created a stored procedure to do some work. Every time i execute this query

  select count(*) from Customer where Email= ....

in my database i get a count 0 but in my stored procedure the result is always 1 without any error.This is the code inside my stored procedure.

  BEGIN
  START TRANSACTION;
  SET @var=(select count(*) from Customer where Email=email);
  select @var;
  if (@var>0) then
    update Customer Set Password=temppass,TemporaryPassword=temppass where 
    Email=email;
    COMMIT;
  ELSE
    ROLLBACK;
  END IF;
  END

Any ideas please?Thanks!

Gordon Linoff · Accepted Answer · 2017-09-04 15:25:11Z

1

The problem is your condition Email = email. This is always going to be true -- because both references to email are referring to the same thing, the email column in the table.

Call the variable by a different name. I often use the suffix v_, so where customer.email = v_email.

That said, I think you can just do:

begin
    update Customer c
        set c.Password = v_temppass,
            c.TemporaryPassword = v_temppass
    where c.email = v_email;
end;

I don't see what value the transaction gives.

I assume that you are not storing passwords as clear-text. That is definitely a no-no if you care at all about security and access.

answered Sep 4, 2017 at 15:25

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

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

1 Comment

srv8 Over a year ago

It worked!By the way the passwords are encrypted because i really care about security and access.Thank you very much!

Collectives™ on Stack Overflow

MySql variable stored with select count statement always returns 1

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related