SELECT a.card_no, a.EXPIRE, a.unit_price, a.start_date
FROM sales.price_range a, sales.quotation_sheet_detail b
WHERE a.card_no = b.card_no
AND b.quotation_no = '2009030037'
AND EXPIRE = 'N'
执行表连接查询得出以下数据:
card_no expire unit_price start_date
12010B0172-1 N 0.0207 2009/3/7
12010B0172-1 N 0.0203 2009/3/26
12010B0174-1 N 0.0207 2009/3/20
12010B0174-1 N 0.0203 2009/3/26
12010B0175-1 N 0.0207 2009/3/20
12010B0175-1 N 0.0203 2009/3/26
12010B0176-1 N 0.0207 2009/3/20
12010B0176-1 N 0.0203 2009/3/26
12010B0177-1 N 0.0207 2009/3/20
12010B0177-1 N 0.0203 2009/3/26
12010B0129-1 N 0.0207 2008/12/2
12010B0129-1 N 0.0203 2009/3/26
12010B0130-1 N 0.0207 2008/11/26
12010B0130-1 N 0.0203 2009/3/26
12010B0132-1 N 0.0207 2008/12/2
12010B0132-1 N 0.0203 2009/3/26现在我想将日期不是2009/3/26的expire列更新为Y,如何更新?我的测试SQL把所有expire列都更新了....
UPDATE sales.price_range
SET EXPIRE = 'Y'
WHERE EXISTS (
SELECT 'x'
FROM (SELECT a.card_no, a.EXPIRE, a.unit_price, a.start_date
FROM sales.price_range a, sales.quotation_sheet_detail b
WHERE a.card_no = b.card_no
AND b.quotation_no = '2009030037'
AND EXPIRE = 'N')
WHERE TO_CHAR (start_date, 'yyyy.mm.dd') <> '2009.03.26')
紧急!!~
FROM sales.price_range a, sales.quotation_sheet_detail b
WHERE a.card_no = b.card_no
AND b.quotation_no = '2009030037'
AND EXPIRE = 'N'
执行表连接查询得出以下数据:
card_no expire unit_price start_date
12010B0172-1 N 0.0207 2009/3/7
12010B0172-1 N 0.0203 2009/3/26
12010B0174-1 N 0.0207 2009/3/20
12010B0174-1 N 0.0203 2009/3/26
12010B0175-1 N 0.0207 2009/3/20
12010B0175-1 N 0.0203 2009/3/26
12010B0176-1 N 0.0207 2009/3/20
12010B0176-1 N 0.0203 2009/3/26
12010B0177-1 N 0.0207 2009/3/20
12010B0177-1 N 0.0203 2009/3/26
12010B0129-1 N 0.0207 2008/12/2
12010B0129-1 N 0.0203 2009/3/26
12010B0130-1 N 0.0207 2008/11/26
12010B0130-1 N 0.0203 2009/3/26
12010B0132-1 N 0.0207 2008/12/2
12010B0132-1 N 0.0203 2009/3/26现在我想将日期不是2009/3/26的expire列更新为Y,如何更新?我的测试SQL把所有expire列都更新了....
UPDATE sales.price_range
SET EXPIRE = 'Y'
WHERE EXISTS (
SELECT 'x'
FROM (SELECT a.card_no, a.EXPIRE, a.unit_price, a.start_date
FROM sales.price_range a, sales.quotation_sheet_detail b
WHERE a.card_no = b.card_no
AND b.quotation_no = '2009030037'
AND EXPIRE = 'N')
WHERE TO_CHAR (start_date, 'yyyy.mm.dd') <> '2009.03.26')
紧急!!~
SET EXPIRE = 'Y'
WHERE EXISTS (
SELECT 1
FROM (SELECT a.card_no, a.EXPIRE, a.unit_price, a.start_date
FROM sales.price_range a, sales.quotation_sheet_detail b
WHERE a.card_no = b.card_no
AND b.quotation_no = '2009030037'
AND EXPIRE = 'N') t2
WHERE t1.card_no=t2.card_no AND TO_CHAR (t2.start_date, 'yyyy.mm.dd') <> '2009.03.26')
SET EXPIRE = 'Y'
WHERE EXISTS (
SELECT 1
FROM sales.price_range a, sales.quotation_sheet_detail b
WHERE a.card_no = b.card_no
AND b.quotation_no = '2009030037'
AND EXPIRE = 'N'
and c.card_no = a.card_no --card_no应该是主键吧
and TO_CHAR (a.start_date, 'yyyy.mm.dd') <> '2009.03.26')
SET EXPIRE = 'Y'
WHERE CARD_NO IN
(SELECT CARD_NO FROM sales.quotation_sheet_detail WHERE quotation_no = '2009030037')
AND EXPIRE = 'N'
AND TO_CHAR (start_date, 'yyyy.mm.dd') <> '2009.03.26'
where card_no in
(select card_no from
(SELECT a.card_no,a.EXPIRE, a.unit_price, a.start_date
FROM sales.price_range a, sales.quotation_sheet_detail b
WHERE a.card_no = b.card_no
AND b.quotation_no = '2009030037'
AND EXPIRE = 'N'
and a.start_date<>2009/3/26))