sql - 我正在尝试执行以下查询，但它总是报错

Question

架构

Tenants (HouseID, ApartmentNumber, LeaseTenantSSN, LeaseStartDate(not null), LeaseExpirationDate(can be null),
 Rent, LastRentPaidDate, RentOverdue)
NewRentPayments (HouseID, ApartmentNumber, LeaseTenantSSN(not null), Rent(can be null), DatePaid)

查询

INSERT INTO Tenants (x.HouseID,x.ApartmentNumber,x.LeaseTenantSSN,CURRENT_DATE,NULL,x.Rent,x.Rent,FALSE)
SELECT  x.HouseID, x.ApartmentNumber,x.LeaseTenantSSN,CURRENT_DATE,NULL,x.Rent,x.Rent                  
FROM NewRentPayments x
WHERE x.HouseID NOT IN (select HouseID
                FROM Tenants)
      OR x.ApartmentNumber NOT IN (SELECT ApartmentNumber
                   FROM Tenants
                   WHERE Tenants.HouseID=x.HouseID);

我总是在 CURRENT_DATE，NULL 收到错误。它说在 CURRENT_DATE 或附近出现语法错误

Answer 1

你的语法不对。 INSERT INTO 之后应该是您计划插入数据的列名列表，使用以下 SELECT 作为源。这样的事情应该有效:

INSERT INTO Tenants (HouseID, ApartmentNumber, LeaseTenantSSN, LeaseStartDate,
                     LeaseExpirationDate, Rent, LastRentPaidDate, RentOverdue)
SELECT x.HouseID, x.ApartmentNumber, x.LeaseTenantSSN, CURRENT_DATE, NULL, x.Rent,
       x.DatePaid, FALSE
FROM NewRentPayments x
WHERE x.HouseID NOT IN (SELECT HouseID FROM Tenants) OR
      x.ApartmentNumber NOT IN (SELECT ApartmentNumber FROM Tenants
                                WHERE Tenants.HouseID=x.HouseID);

我为 RentOverdue 值选择了 FALSE，这似乎符合您的逻辑。