通过在sql中连接2个表来插入 [英] insertion by joining 2 tables in sql
问题描述
这里我有2张桌子
latlongtransactions
(
devId,
lat,
lon
)
和
devicemaster
(
devId,
密码
)
现在我的任务是我必须通过将devId与devicemaster匹配来在latlongtransactions表中插入lat和lon值用户输入密码和devId的表格。
比方说,我给了
12345作为devId,
9999作为密码,>
lat为10.2和
lon为10.9作为输入
现在任务是lat和lon将插入latlongtransactions带有提到的devId的表但首先它将检查来自devicemaster表的devId和密码。(这里是devicemaster表devId是主键和另一个表的外键。)
等待您的回复。
您好,
查看此... < br $>
创建 程序 Insertvalues
(
@ pa_devId int , - 或根据您的数据类型
@ pa_pass varchar ( 100 ), - 或根据您的数据类型
@ pa_lat decimal (< span class =code-digit> 10 , 2 ), - 或根据您的数据类型
@ pa_lon decimal ( 10 , 2 ), - 或根据您的数据类型
@ OutPutMess varchar ( 20 ) - 或根据您的要求
)
作为
开始
设置 @ OutPutMess = ' '
声明 @ cnt int
设置 @ cnt = 0
选择 @ cnt =计数(*)来自 devicemaster 其中 devId = @ pa_devId 和密码= @ pa_pass
如果 @ cnt > 0
开始
插入 进入 latlongtransactions(devId,lat,lon)
值( @ pa_devId , @ pa_lat , @ pa_lon )
设置 @ OutPutMess = ' 完成' - 或根据您的要求
end
else
开始
设置 @ OutPutMess = ' 错误' - 或根据您的要求
结束
选择 @OutPutMess
结束
希望这会对你有所帮助。
干杯
INSERT INTO latlongtransactions(devId,lat,lon)
SELECT 12345 , 10 。 2 , 10 。 9
FROM dual
WHERE EXISTS ( SELECT * FROM devicemaster
WHERE devId = < span class =code-digit> 12345
AND password = 9999 )
here i have 2 tables
latlongtransactions
(
devId,
lat,
lon
)
and
devicemaster
(
devId,
password
)
Now my task is i have to insert lat and lon value in the latlongtransactions table by matching the devId with devicemaster table where password and devId is being checked inputted by the user.
Say for example, i am giving
12345 as devId,
9999 as password ,
lat as 10.2 and
lon as 10.9 as inputs
Now the task is lat and lon will be inserted into latlongtransactions table with the mentioned devId but firstly it will check the devId and password from devicemaster table.(here in devicemaster table devId is the primary key and a foreign key to the another table.)
Waitng for your reply.
Hi,
Check this...
Create Procedure Insertvalues ( @pa_devId int,--or as per your datatype @pa_pass varchar(100),--or as per your datatype @pa_lat decimal(10,2),--or as per your datatype @pa_lon decimal(10,2),--or as per your datatype @OutPutMess varchar(20)--or as per your requirement ) As Begin Set @OutPutMess='' Declare @cnt int Set @cnt=0 Select @cnt = Count(*) from devicemaster where devId=@pa_devId and password=@pa_pass if @cnt > 0 Begin insert into latlongtransactions (devId, lat, lon) values (@pa_devId,@pa_lat,@pa_lon) Set @OutPutMess='Done'--or as per your requirement end else Begin Set @OutPutMess='Error'--or as per your requirement End Select @OutPutMess End
Hope this will help you.
Cheers
INSERT INTO latlongtransactions(devId, lat, lon) SELECT 12345 , 10.2, 10.9 FROM dual WHERE EXISTS (SELECT * FROM devicemaster WHERE devId = 12345 AND password = 9999 )
这篇关于通过在sql中连接2个表来插入的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!