在MySQL中,可以以下这么更新
UPDATE mytable t1,
(
SELECT col FROM mytable WHERE dt < 10 limit 1
) t2
SET t1.col = t2.col
WHERE t1.cd = t2.cd但是换成postgreSQL数据库,就出错了,t1后逗号那个错误
好像postgreSQL的更新UPDATE 后面只能跟一个表怎么解决呢
UPDATE mytable t1,
(
SELECT col FROM mytable WHERE dt < 10 limit 1
) t2
SET t1.col = t2.col
WHERE t1.cd = t2.cd但是换成postgreSQL数据库,就出错了,t1后逗号那个错误
好像postgreSQL的更新UPDATE 后面只能跟一个表怎么解决呢
解决方案 »
- 请教怎样把数据库中的表和数据批量生成sql语句?
- varchar与char的基础疑惑问题
- mssql 转mysql自定义函数的问题
- mysql_query(&zhqy,"select * from shop")时 报错Commands out of sync
- 日期转换字符
- 求救: A strong SA password is required for security reasons
- 使用OleDbConnection连接MySql出错
- 大家看看这个建表语句为什么报错
- Mysql 中 mediumtext 类型 字段 为空怎么判断求大神
- jdbc删除问题实现删除数据库中的表任意一列,不能实现求助
- 请问mysql有没有可以对查询结果标记序号的COL隐藏列
- 关于有AUTO_INCREMENT 的表怎么插入
set col = (select col FROM mytable WHERE dt < 10 and col= t1.col limit 1)
select UserCD,OperationDT,1 idx from
(SELECT * FROM daily_reports
where OperationDT < '2010-04-18 02:00:00' and UserCD = '00002073'order by OperationDT desc limit 1)as t
union
select UserCD,OperationDT,2 from
(SELECT * FROM daily_reports
where OperationDT < '2010-04-18 14:00:00' and UserCD = '00002073' order by OperationDT desc limit 1)as t1
union
SELECT UserCD,OperationDT,3 FROM daily_reports where OperationDT = '2010-04-18 14:00:00' and UserCD = '00002073'
)d2
left join(
select UserCD,OperationDT,1 idx from
(SELECT * FROM daily_reports
where OperationDT > '2010-04-18 02:00:00' and UserCD = '00002073' order by OperationDT limit 1)as t
union
SELECT UserCD,OperationDT,2 FROM daily_reports where OperationDT = '2010-04-18 14:00:00' and UserCD = '00002073'
union
select UserCD,OperationDT,3 from
(SELECT * FROM daily_reports
where OperationDT > '2010-04-18 14:00:00' and UserCD = '00002073' order by OperationDT limit 1)as t1 )d3
on d2.UserCD = d3.UserCD
and d2.OperationDT = d3.OperationDT
and d2.idx = d3.idx
set d1.CompleteDT=d3.OperationDT
where
d1.UserCD = d2.UserCD
and d1.OperationDT = d2.OperationDT
and d2.idx = d3.idx这是我原来的SQL语句 这个怎么改呢 貌似按照那么改了 我的where语句条件有错
建议你列出你的表结构,并提供测试数据以及基于这些测试数据的所对应正确结果。
参考一下这个贴子的提问方式http://topic.csdn.net/u/20091130/20/8343ee6a-417c-4c2d-9415-fa46604a00cf.html
1. 你的 create table xxx .. 语句
2. 你的 insert into xxx ... 语句
3. 结果是什么样,(并给以简单的算法描述)
4. 你用的数据库名称和版本(经常有人在MS SQL server版问 MySQL)
这样想帮你的人可以直接搭建和你相同的环境,并在给出方案前进行测试,避免文字描述理解上的误差。
set col=t2.col from
(
SELECT col FROM mytable WHERE dt < 10 limit 1
) t2
WHERE t1.cd = t2.cd
(
SELECT col FROM mytable WHERE dt < 10 limit 1
) t2
on t1.cd = t2.cd
set col=t2.col
left join (
select UserCD,OperationDT,1 idx from
(SELECT * FROM daily_reports
where OperationDT < '2010-04-18 02:00:00' and UserCD = '00002073'order by OperationDT desc limit 1)as t
union
select UserCD,OperationDT,2 from
(SELECT * FROM daily_reports
where OperationDT < '2010-04-18 14:00:00' and UserCD = '00002073' order by OperationDT desc limit 1)as t1
union
SELECT UserCD,OperationDT,3 FROM daily_reports where OperationDT = '2010-04-18 14:00:00' and UserCD = '00002073'
)d2
on d1.UserCD = d2.UserCD
and d1.OperationDT = d2.OperationDTleft join (
select UserCD,OperationDT,1 idx from
(SELECT * FROM daily_reports
where OperationDT > '2010-04-18 02:00:00' and UserCD = '00002073' order by OperationDT limit 1)as t
union
SELECT UserCD,OperationDT,2 FROM daily_reports where OperationDT = '2010-04-18 14:00:00' and UserCD = '00002073'
union
select UserCD,OperationDT,3 from
(SELECT * FROM daily_reports
where OperationDT > '2010-04-18 14:00:00' and UserCD = '00002073' order by OperationDT limit 1)as t1 ) d3
on d2.OperationDT = d3.OperationDT
and d2.idx = d3.idx set d1.CompleteDT=d3.OperationDT