带有联接的Oracle Update查询 [英] Oracle Update query with join

问题描述

有拖车桌.第一个是费用，第二个是 expenses_items .

There are tow tables. first one is expenses and second one is expenses_items.

费用表中有2列，分别为expns_id和expns_job_type.

There are 2 columns in expenses table as expns_id and expns_job_type.

expenses_items 表中有2列，分别为expns_id(费用表中的外键)和group.

There are 2 columns in expenses_items table as expns_id (foreign key from expenses table) and group.

我需要更新expenses_items表中的group并将其值设置为development，其中expns_job_type的值在expenses表中像new building一样.

I need to update group in expenses_items table and set it's value as development where value of expns_job_type in expenses table like new building.

我已经尝试过以下查询.但没有用.

I have tried below query. but not worked.

update expenses_items
set expenses_items."group" = 'development'
 join expenses ON 
 expenses_items.expns_id = expenses.expns_id 
where expenses.expns_job_type like 'new building'

我该怎么做?

推荐答案

您可以在where子句中使用子查询来限制您的更新请求:

You can use subquery in where clause to limit your update request:

UPDATE expenses_items ei SET
  group = 'dvelopment'
WHERE EXISTS(
  SELECT expns_id FROM expenses e
  WHERE e.expns_id = ei.expns.id
    AND expns_job_type LIKE 'new building'
)

也可能是拼写错误，但是您使用LIKE子句而不使用通配符.如果您需要完全匹配-使用=而不是LIKE

Also its may be typo, however you using LIKE clause without using wildcards. If you need exact match - use = instead of LIKE

这篇关于带有联接的Oracle Update查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！