SQL关键字FROM附近的语法不正确

I keep getting this error

我一直收到这个错误

Incorrect syntax near the keyword from

关键字来自附近的语法不正确

when I try to run this query. I'm fairly new to SQL so I probably missed something. I'm using SQL Server 2008

当我尝试运行此查询时。我对SQL很新,所以我可能错过了一些东西。我正在使用SQL Server 2008

Basically what I want to do is for the column lastreturndate to have the value = Checkoutdate + Loanperiod. Since loanperiod is in a different table I created a view that contains Checkoutdate, lastreturndate, loanperiod.

基本上我想要做的是使lastreturndate列具有值= Checkoutdate + Loanperiod。由于loanperiod在不同的表中,我创建了一个包含Checkoutdate,lastreturndate,loanperiod的视图。

Example of what I'm trying to achieve

我正在努力实现的例子

Checkoutdate    loanperiod      lastreturndate

  2012-1-01          3           2012-4-01

SQL code:

DECLARE @lastreturndate DATETIME
SET @lastreturndate = dateadd(month,loanperiod,CheckOutDate)
INSERT INTO Loan(lastreturndate) VALUES (@lastreturndate) 
FROM dbo.returndateview

dbo.returndateview = is the view I created

dbo.returndateview =是我创建的视图

Imagelink http://imageshack.us/a/img69/3048/68810818.png to the ERD

Imagelink http://imageshack.us/a/img69/3048/68810818.png到ERD

Note that category in item_details should be called loan period

请注意,item_details中的类别应称为贷款期

Link to my view http://img194.imageshack.us/img194/8200/viewsm.png

链接到我的视图http://img194.imageshack.us/img194/8200/viewsm.png

2 个解决方案

#1

I think you are trying do an UPDATE. Assuming your view and Loan table are related by loanid, you could try

我想你正在努力做一个更新。假设您的查看和贷款表与loanid相关,您可以尝试

Update L set L.lastreturndate = dateadd(month,v.loanperiod,v.CheckOutDate)
From Loat L join dbo.returndateview v
            on L.loanId = v.loanId --You should have this relation

Or, if you could redesign your view as

或者,如果您可以将您的视图重新设计为

create view dbo.returndateview
as
  select Checkoutdate,loanperiod,
       dateadd(month,loanperiod,CheckOutDate) lastreturndate
  from YourTable

#2

Instead of

INSERT INTO Loan(lastreturndate) VALUES (@lastreturndate) 
FROM dbo.returndateview

try

insert into Loan(lastreturndate)
select ___ from dbo.returndateview where ___ = @lastreturndate

(you'll need to fill in the _ with columns from your view).

(您需要使用视图中的列填充_)。

.. or perhaps it is as simple as:

..或者它可能很简单:

DECLARE @lastreturndate DATETIME
SELECT @lastreturndate = dateadd(month,loanperiod,CheckOutDate) FROM dbo.returndateview
INSERT INTO Loan(lastreturndate) VALUES (@lastreturndate)

based on your last edit, I think this is what you want:

基于你的上一次编辑,我想这就是你想要的:

update loan set
    lastReturndate = dateadd(month, detail.loanperiod, CheckOutDate)
from loan
join item on loan.barcode = item.barcode
join item_detals detail on item.isbn = detail.isbn

if you need your view, I believe you need to change it to:

如果您需要您的观点,我相信您需要将其更改为:

create view returnDateView as
select
   detail.loanperiod,
   loan.CheckOutDate,
   loan.LastReturndate
from loan
join item on loan.barcode = item.barcode
join item_detals detail on item.isbn = detail.isbn

#1