更新从 SQL Server 转换为 Oracle 中的 SQL

发布于 2024-07-27 18:35:58 字数 365 浏览 6 评论 0原文

我正在尝试在 Oracle 中创建更新语句。这是 SQL Server 中的版本：

  UPDATE ev
  SET   
     (ev.station_type = t.st_type, ev.adt = t.adt  )
  FROM  source ev 
  JOIN  dataTbl t
  ON ev.counterID = t.counterID

有两个表源表 counterID 是主键 dataTBL 表中 counterID 为外键我试图将数据从 dataTBL 获取到源表。

如果有人帮助创建 Oracle 版本的更新，我将不胜感激。谢谢，格林纳

原文

I am trying to create an update statement in Oracle.
Here it is version in SQL Server:

  UPDATE ev
  SET   
     (ev.station_type = t.st_type, ev.adt = t.adt  )
  FROM  source ev 
  JOIN  dataTbl t
  ON ev.counterID = t.counterID

There are two tables
source table were counterID is primary key
dataTBL table were counterID is Foreign key
I am tring to get data from dataTBL to souce table.

I would appreciate if someone helps to create an Oracle version of the update.
Thanks,
Greener

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

栖迟 2024-08-03 18:35:58

您想要使用此技术：

  UPDATE ( SELECT ev.station_type
                , t.st_type
                , ev.adt ev_adt
                , t.adt t_adt
             FROM  source ev
                   JOIN dataTbl t
                     ON ev.counterID = t.counterID
         )
     SET station_type = st_type
       , ev_adt = t_adt
  ;

子查询创建结果集，此时您可以使用 SET 子句根据需要更新列。

You want to use this technique:

  UPDATE ( SELECT ev.station_type
                , t.st_type
                , ev.adt ev_adt
                , t.adt t_adt
             FROM  source ev
                   JOIN dataTbl t
                     ON ev.counterID = t.counterID
         )
     SET station_type = st_type
       , ev_adt = t_adt
  ;

The subquery creates your resultset, at which point you can use the SET clause to update the columns however you wish.

回复收藏 0 原文

茶色山野 2024-08-03 18:35:58

我相信以下语法可以工作（手头没有Oracle可以检查）：

UPDATE ev
SET (station_type, adt ) = (
   SELECT (t.st_type, t.adt)
   FROM dataTbl t
   WHERE t.counterId = ev.counterId
)
WHERE ev.counterId IN (SELECT t.counterID FROM t);

编辑：WHERE子句重复看起来很难看，但正如@Steve指出的那样，需要避免在没有匹配的行中插入空值dataTbl 中的条目（当然，如果您的用例有可能的话）。如果您确实需要此类预防措施，@Steve 的基于连接的解决方案可能更可取。

I believe the following syntax would work (no Oracle at hand to check):

UPDATE ev
SET (station_type, adt ) = (
   SELECT (t.st_type, t.adt)
   FROM dataTbl t
   WHERE t.counterId = ev.counterId
)
WHERE ev.counterId IN (SELECT t.counterID FROM t);

Edit: the WHERE clause repetition looks ugly, but as @Steve points out it's needed to avoid inserting nulls in rows that have no matching entry in dataTbl (if that's a possibility in your use case, of course). If you do need such precautions, @Steve's join-based solution may be preferable.

回复收藏 0 原文

~没有更多了~