我正在使用JDBC连接并尝试执行DDL命令的java。这里我对一个特定的流程有疑问,这可能吗?如果有,你能举例说明一下怎么做,怎么做吗?
我正在尝试选择从包含item_id, sale_price, description, barcode列的item表中获取数据,并希望更新条形码数据item_id = 9和在item_duplicate表格不更新项表格但item_dupliacte表应该包含条形码。
my item_duplicate table
项目表
MERGE item_duplicate AS D
USING item AS I
ON (D.item_id = I.item_id )
WHEN MATCHED
THEN UPDATE set D.part_no='new part'
WHEN NOT MATCHED BY D
THEN
INSERT (item_id,part_no,sale_price,description,barcode)
SELECT i.ITEM_ID,i.PART_NO,i.SALE_PRICE,i.DESCRIPTION,b.BARCODE
FROM item i
JOIN item_barcode b
ON b.ITEM_ID = i.ITEM_ID
WHERE i.ITEM_ID = ?
WHEN NOT MATCHED BY I
THEN DELETE;
一个简单的insert到select from。
CREATE TABLE dbo.TEST
(
item_id INT NOT NULL
, barcode VARCHAR (20) NULL
, sale_price DECIMAL (14, 2) NULL
, description VARCHAR (100) NULL
, PRIMARY KEY (item_id)
)
CREATE TABLE dbo.TEST_COPY
(
item_id INT NOT NULL
, barcode VARCHAR (20) NULL
, sale_price DECIMAL (14, 2) NULL
, description VARCHAR (100) NULL
, PRIMARY KEY (item_id)
)
INSERT INTO TEST_COPY (item_id, barcode, sale_price, description) SELECT item_id, '9999' as barcode, sale_price, description FROM TEST WHERE item_id = 9