How can I make this possible..really need advice? I want to get the id where my condition is met, then used it in my queries.
IF EXISTS (Select sn_id as snid FROM device.sn WHERE dname_id = 62 and sn_value = '123415')
BEGIN
SELECT MAX(id) AS maxid FROM device.list
INSERT INTO parts (sn_id,device_id) VALUES (snid, maxid)
END
ELSE
BEGIN
PRINT 'id does not exist'
return
END
You can use variables to store the results from the two queries and then use those values in your INSERT statement.
If you're using Microsoft SQL Server then the following may work (but there may be superficial syntax errors as it hasn't been tested). Note that I've assumed the type of your columns is int.
DECLARE @snid int
SET @snid = NULL
Select @snid = sn_id FROM device.sn WHERE dname_id = 62 and sn_value = '123415'
IF @snid IS NULL
BEGIN
PRINT 'id does not exist'
END
ELSE
BEGIN
DECLARE @maxid int
SELECT @maxid = MAX(id) AS maxid FROM device.list
INSERT INTO parts (sn_id,device_id) VALUES (@snid, @maxid)
END
In SQLServer. This script at first insert records and after checks count of the inserted rows
INSERT INTO parts (sn_id, device_id)
SELECT sn_id, (SELECT MAX(id) FROM device.list)
FROM device.sn
WHERE dname_id = 62 and sn_value = '123415'
IF @@ROWCOUNT = 0 PRINT 'id does not exist'
Declare @snid int=null
Declare @maxid int=1 -- if no value exists in device.list table
set @snid = (select sn_id from device.sn WHERE dname_id = 62 and sn_value = '123415')
set @maxid = (select MAX(id) AS maxid FROM device.list)
if @snid is not null
Begin
insert into parts(sn_id,device_id)
values(@snid,@maxid)
End
else
Begin
print 'ID does not exist'
End
