用INNER JOIN更新 [英] UPDATE with INNER JOIN

问题描述

我正在使用JavaDB并在同一个数据库中处理两个表.

I'm using JavaDB and working with two tables in the same database.

当车辆表的SellDate在指定日期之前时，我试图用车辆"表的"SellDate"列中的值更新"SalesResp"表中的PrevSales列.我通过使用两个表中都存在的"VIN"列来匹配两个表之间的数据.

I'm trying to update the PrevSales column in the "SalesResp" table with the values that are in the "SellDate" column of the "Vehicles" table when the Vehicle table's SellDate is before a the specified date. I'm matching the data between the two tables by using the "VIN" column that is present in both tables.

在我的代码SQL状态42x01中，我不断收到围绕内部联接的错误.

I continually get errors surrounding the inner join in my code, SQL state 42x01.

UPDATE SALESRESP JOIN VEHICLES ON (SALESRESP.VIN = VEHICLES.VIN) 
SET SALESRESP.PrevSale = VEHICLES.SELLDATE WHERE 
(((VEHICLES.SELLDATE)<'2013-09-24'));

我尝试了各种配置，交换了INNER JOIN和SET放置位置，并尝试包含"FROM"子句.似乎没有任何作用.有什么建议吗?

I've tried various configurations, swapping the INNER JOIN and the SET placements, I've tried including a "FROM" clause. Nothing seems to work. Any advice?

确切错误代码:

Error code 30000, SQL state 42X01: Syntax error: Encountered "JOIN" at line 1, column 19.

推荐答案

使用以下代码，查看并替换您的日期而不是sysdate.

Use the following code and see, replace your date instead of sysdate.

UPDATE SALESRESP  
SET SALESRESP.PrevSale  = (SELECT VEHICLES.SELLDATE FROM VEHICLES 
WHERE SALESRESP.VIN = VEHICLES.VIN  AND
VEHICLES.SELLDATE <'2013-09-24');

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