mysql存储过程update值为参数值,MYSQL存储过程的更新变量为0

I have the following stored procedure in a MYSQL database. The stored procedure gets lon,lat and than I'm doing an update on a different database and table.

DROP PROCEDURE IF EXISTS annuals.updateSalesFlorida;

CREATE DEFINER=`dddd`@`%` PROCEDURE `updateSales`(propertyId int)

BEGIN

DECLARE lat FLOAT;

DECLARE lon FLOAT;

SELECT SitusLongitude,SitusLatitude

INTO lon,lat

FROM annuals.florida

WHERE PropertyID=propertyId

LIMIT 1 FOR UPDATE;

UPDATE sales.florida

SET

`SitusLongitude` = lon,

`SitusLatitude` = lat

WHERE PROPERTYUNIQUEID=propertyId;

END;

Every time I run the stored procedure the SitusLongitude and SitusLatitude columns are 0. I know for a fact that the previous selected SitusLongitude and SitusLatitude have actual values in there. For some reason the variables are not being set in lat,lon. Any idea what I'm dong wrong?

解决方案

The problem is that the procedure parameter has the same name as a column in the tables. When you refer to propertyid in the queries, it uses the column, not the parameter. Column and variable names are case-insensitive, so it doesn't matter that you spelled one of them PropertyID and the other propertyId.

Use a different name for the parameter, e.g. p_propertyId

Also, there's no need for two queries, you can do it in one with a JOIN.

UPDATE sales.florida AS s

CROSS JOIN (

SELECT *

FROM annuals.florida

WHERE propertyId = p_propertyId

LIMIT 1) AS a

SET s.SitusLongitude = a.SitusLongitude, s.SitusLatitude = a.SitusLatitude

WHERE s.PROPERTYUNIQUEID = p_propertyId

Note that using LIMIT without ORDER BY means that the row that's selected will be unpredictable.