MySQL转postgreSQL 更新问题 100分
在MySQL中,可以以下这么更新
-
SQL code
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 code
UPDATE mytable t1 set col = (select col FROM mytable WHERE dt < 10 and col= t1.col limit 1)
UPDATE mytable t1
set col=t2.col from
(
SELECT col FROM mytable WHERE dt < 10 limit 1
) t2
WHERE t1.cd = t2.cd
UPDATE mytable t1 inner join
(
SELECT col FROM mytable WHERE dt < 10 limit 1
) t2
on t1.cd = t2.cd
set col=t2.col
update daily_reports d1
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.OperationDT
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.OperationDT = d3.OperationDT
and d2.idx = d3.idx
set d1.CompleteDT=d3.OperationDT