我的sql更新过程 [英] my sql update procedure

问题描述

我对此查询有疑问.请有人帮助我.

i have some doubt in this query .please someone help me.

CREATE PROCEDURE sp_insert_userdetailss
(
)
BEGIN
IF EXISTS(SELECT `name`,`email`,`phono no` FROM user_details 
WHERE email=email)
UPDATE user_details
SET `name`=`name`,email=email,`phone no`=`phone no`,photo=photo;
ELSE
INSERT INTO user_details(`name`,email,`phone no`,`photo`)
VALUES(`name`,`email`,`phone no`,`photo`);
SELECT MAX(user_details.id)
FROM user_details
INNER JOIN city ON user_details.city_id=city.id
INNER JOIN area ON user_details.area_id=area.id
INNER JOIN usertype ON user_details.type_id=usertype.id;
END;

我收到错误->

and i am getting the error->

[SQL] CREATE PROCEDURE sp_insert_userdetailss
(
)
BEGIN
IF EXISTS(SELECT `name`,`email`,`phono no` FROM user_details 
WHERE email=email)
UPDATE user_details
SET `name`=`name`,email=email,`phone no`=`phone no`,photo=photo;
ELSE
INSERT INTO user_details(`name`,email,`phone no`,`photo`)
VALUES(`name`,`email`,`phone no`,`photo`);
SELECT MAX(user_details.id)
FROM user_details
INNER JOIN city ON user_details.city_id=city.id
INNER JOIN area ON user_details.area_id=area.id
INNER JOIN usertype ON user_details.type_id=usertype.id;
END;

[Err] 1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'UPDATE user_details
SET `name`=`name`,email=email,`phone no`=`phone no`,photo=p' at line 7

推荐答案

将列名周围的引号更改为方括号，将引号更改为普通引号字符:

Change the quotes around your column names to square brackets, and the quotes to normal quote characters:

UPDATE user_details
SET [name]='name',email=email,[phone no]='phone no',photo=photo;

错误1064用于保留字，请检查不允许使用哪个字.


http://dev.mysql.com/doc/refman/5.5/en/reserved- words.html [^ ]

ERROR 1064 is for Reserved Words, check which word is not permitted.


http://dev.mysql.com/doc/refman/5.5/en/reserved-words.html[^]

尝试这样的事情

Try somthing like this

CREATE PROCEDURE sp_insert_userdetailss
           @name nvarchar(100), 
           @email nvarchar(100), 
           @phon_no nvarchar(100), 
           @photo nvarchar(100)
As
BEGIN
IF EXISTS(SELECT name,email,[phono no] FROM user_details WHERE email=@email)
	UPDATE user_details
	SET name=@name,email=@email,[phono no]=@phon_no,photo=@photo;
ELSE
	INSERT INTO user_details(name,email,[phono no],photo)
	values (@name,@email,@phon_no,@photo)

SELECT MAX(user_details.id)
FROM user_details
INNER JOIN city ON user_details.city_id=city.id
INNER JOIN area ON user_details.area_id=area.id
INNER JOIN usertype ON user_details.type_id=usertype.id;
END;

这篇关于我的sql更新过程的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！