我现在的语句是这样
INSERT INTO data_dttj SELECT * FROM macau_data c where c.id not in(SELECT a.id FROM macau_data a, data_dttj b where a.id=b.id ) and gg<'9' and gg<>'1'我想再加一个条件,就是macau_data表的zd字段要大于data_dttj表zd字段才插入,我就这样写,可是错了
INSERT INTO data_dttj SELECT * FROM macau_data c where c.id not in(SELECT a.id FROM macau_data a, data_dttj b where a.id=b.id ) and gg<'9' and gg<>'1' and a.zd>b.zd请问应该怎样写?帮我看看。谢谢!
INSERT INTO data_dttj SELECT * FROM macau_data c where c.id not in(SELECT a.id FROM macau_data a, data_dttj b where a.id=b.id ) and gg<'9' and gg<>'1'我想再加一个条件,就是macau_data表的zd字段要大于data_dttj表zd字段才插入,我就这样写,可是错了
INSERT INTO data_dttj SELECT * FROM macau_data c where c.id not in(SELECT a.id FROM macau_data a, data_dttj b where a.id=b.id ) and gg<'9' and gg<>'1' and a.zd>b.zd请问应该怎样写?帮我看看。谢谢!
INSERT INTO data_dttj SELECT * FROM macau_data c
WHERE c.id not in(select a.id from macau_data a, data_dttj b where a.id=b.id )
and c.gg<'9' and c.gg<>'1'
and c.zd > (select max(zd) as zd from data_dttj) /*新条件*/
INSERT INTO data_dttj SELECT * FROM macau_data c
WHERE c.id not in(select a.id from macau_data a, data_dttj b where a.id=b.id )
and c.gg<'9' and c.gg<>'1'
and c.zd > (select max(zd) as zd from data_dttj) 这样不对,符合这个条件的插入不了数据
-----------------------------------------------------------------------------------
语法应该没有问题.
"c.zd > (select max(zd) as zd from data_dttj) "这个条件是指macau_data表中zd列大于data_dttj表中所有zd列的行.如果是这个条件导致了插入的数据不符合要求,请楼主指明一下主题中
"我想再加一个条件,就是macau_data表的zd字段要大于data_dttj表zd字段才插入"
这段话的确切含义.
更好的办法是请楼主列些这二个表的测试数据,然后写出希望的结果.
macau_data表
id zd
01 2
02 1.8
03 1.5data_dttj表
id zd
01 1.9
02 1.8
03 1.6这个时候macau_data表只能插入 01 2.0 这行数据到 data_dttj
就是要这个效果我现在的语句是这样
INSERT INTO data_dttj SELECT * FROM macau_data c where c.id not in(SELECT a.id FROM macau_data a, data_dttj b where a.id=b.id ) and gg<'9' and gg<>'1'应该要改成怎样?谢谢!
INSERT INTO data_dttj SELECT * FROM macau_data c where c.id not in(SELECT a.id FROM macau_data a, data_dttj b where a.id=b.id and a.zd<=b.zd
) and gg<'9' and gg<>'1'
) and gg<'9' and gg<>'1'
这样的话就会报错:
由于将在索引、 主关键字、或关系中创建重复的值,请求对表的改变没有成功。 改变该字段中的或包含重复数据的字段中的数据,删除索引或重新定义索引以允许重复的值并再试一次。我用not in就是为了防止这个错误的,请问怎么办呢?
set zd=a.zd,col1=a.col1,.....
FROM macau_data a, data_dttj b where a.id=b.id and a.zd>b.zd
and a.gg<'9' and a.gg<>'1'INSERT INTO data_dttj SELECT * FROM macau_data c where c.id not in(SELECT a.id FROM macau_data a, data_dttj b where a.id=b.id ) and gg<'9' and gg<>'1'
INSERT INTO data_dttj SELECT * FROM macau_data c where c.id not in(SELECT a.id FROM macau_data a, data_dttj b where a.id=b.id ) and gg<'9' and gg<>'1'
这样就是没有重复的,可是我现在想加个判断 a.zd>b.zd 可以写在 最后面吗?还是要怎么改?请指教
怎么感觉lz是个MM?还真做不到一个语句,写个存储过程吧
-----------------------------------------------------------------------------------
如果是这样,macau_data表的01 2.0这行怎么能符合条件呢?
不太明白楼主的需求.
既然data_dttj表的id不允许重复,那么macau_data表中任何与data_dttj的id同名的行是无法插入到data_dttj表中了.请楼主最好把表之间的业务逻辑展示一下,好让大家对你的业务需求有个清晰的认识,然后帮你出出主意,比这样没效率的猜要好.
select a.id,a.zd
from macau_data a , data_dttj b
where a.id=b.id and a.zd>b.zd你看看这个行不行
-------
不可能的,你插入的都是原来没有的id,有个not in把关
http://community.csdn.net/Expert/topic/4995/4995116.xml?temp=.1583521
更晕冒牌今天脾气还是很好的,佩服自己一下
不知道和
select * from macau_data c where c.id not in(select id from dttj)
有什么区别啊
INSERT INTO data_dttj SELECT * FROM macau_data c where c.id not in(SELECT a.id FROM macau_data a, data_dttj b where a.id=b.id ) and gg<'9' and gg<>'1'
如果 要我想再加一个条件,就是macau_data表的zd字段要大于data_dttj表zd字段才插入比如
macau_data表
id zd
01 2
02 1.8
03 1.5data_dttj表
id zd
01 1.9
02 1.8
03 1.6这个时候macau_data表只能插入 01 2 这行数据到 data_dttj
就是要这个效果,请问在我那句基础上加这个效果应该怎么改?
----------------------------------------------------------------------------------
可是data_dttj表中已经有id = '01'了,把01 2这行插入到data_dttj表中会导致id值重复的错误.假设不考虑id值重复的错误,楼主希望插入后data_dttj表的效果是这样的吗?:
data_dttj表
id zd
01 1.9
02 1.8
03 1.6
01 2 /*新插入的行*/
WHERE c.id not in(select a.id from macau_data a, data_dttj b where a.id=b.id )
and c.gg<'9' and c.gg<>'1'
and c.zd > (select max(zd) as zd from data_dttj) 这样就不报错,但是插入不了我要的效果,是哪里错了?
INSERT INTO data_dttj SELECT * FROM macau_data c where c.id not in(SELECT a.id FROM macau_data a, data_dttj b where a.id=b.id ) and gg<'9' and gg<>'1'
如果 要我想再加一个条件,就是macau_data表的zd字段要大于data_dttj表zd字段才插入比如
macau_data表
id zd
01 2
02 1.8
03 1.5data_dttj表
id zd
01 1.9
02 1.8
03 1.6结果
data_dttj表
id zd
01 2
02 1.8
03 1.6就是要这个效果,请问在我那句基础上加这个效果应该怎么改?
INSERT INTO data_dttj SELECT * FROM macau_data c where c.id not in(SELECT a.id FROM macau_data a, data_dttj b where a.id=b.id and a.zd<=b.zd
) and gg<'9' and gg<>'1'
-----------------------
这么写报不报错先不说.按照楼主的逻辑,起码也得把最后一个and换成or吧...
就象rea1gz(冒牌realgz V0.3)朋友说的那样,要使用二个SQL才能实现楼主的要求,因为有替换同名id的过程.
这样试试:
declare @macau_data table(id varchar(10),zd decimal(10,2))
insert @macau_data
select '01',2 union all
select '02',1.8 union all
select '03',1.5declare @data_dttj table(id varchar(10),zd decimal(10,2))
insert @data_dttj
select '01',1.9 union all
select '02',1.8 union all
select '03',1.6----插入符合条件的id不重复行
INSERT INTO @data_dttj SELECT * FROM @macau_data c
WHERE c.id not in(select a.id from @macau_data a, @data_dttj b where a.id=b.id )
--and c.gg<'9' and c.gg<>'1' /*为了测试,屏蔽了该条件*/
and c.zd > (select max(zd) as zd from @data_dttj)
----更新同名id的zd值
update a set zd = b.zd from @data_dttj a,@macau_data b where a.id = b.id and a.zd < b.zd
--and c.gg<'9' and c.gg<>'1' /*为了测试,屏蔽了该条件*/----查看
select * from @data_dttj
Windows Server2003 + sp1
SQLSERVER2000 + sp4结果是:
id zd
---------------------------------
01 2.00
02 1.80
03 1.60
update a set zd = b.zd,kd = b.kd from data_dttj a,macau_data b where a.id = b.id and ((a.zd < b.zd) or (a.kd < b.kd))这样报错:语法错误 (操作符丢失) 在查询表达式 'b.kd from data_dttj a' 中。
declare @macau_data table(id varchar(10),zd decimal(10,2))
insert @macau_data
select '01',2 union all
select '02',1.8 union all
select '03',1.5declare @data_dttj table(id varchar(10),zd decimal(10,2))
insert @data_dttj
select '01',1.9 union all
select '02',1.8 union all
select '03',1.6----插入符合条件的id不重复行
INSERT INTO @data_dttj SELECT * FROM @macau_data c
WHERE c.id not in(select a.id from @macau_data a, @data_dttj b where a.id=b.id )
--and c.gg<'9' and c.gg<>'1' /*为了测试,屏蔽了该条件*/
and c.zd > (select max(zd) as zd from @data_dttj)
----更新同名id的zd值
update a set zd = b.zd from @data_dttj a,@macau_data b where a.id = b.id and a.zd < b.zd
--and c.gg<'9' and c.gg<>'1' /*为了测试,屏蔽了该条件*/----查看
select * from @data_dttj
update a set zd = b.zd,kd = b.kd from data_dttj a,macau_data b where a.id = b.id and ((a.zd < b.zd) or (a.kd < b.kd))
这句看看会不会报错
------------------------------------------------------------------------------------
从你提出那个操作符丢失错误开始,我就怀疑楼主用的是不是SQLSERVER.因为我用SQLSERVER时从来没遇到这种错误.可以肯定是楼主的代码与ACCESS的JET-SQL语法不兼容.请楼主参考一下ACCESS的语法吧.我不熟悉ACCESS.
所谓的基本差不多,是指都符合SQL ANSI-92标准,在SELECT,UPDATE,DELETE时语法基本相同,但各种DBMS系统会在这些相同的基础上进行扩展演化的.
还有
update a set zd = b.zd,kd = b.kd from data_dttj a,macau_data b where a.id = b.id and ((a.zd < b.zd) or (a.kd < b.kd))
这句的语法应该是错误的