Oracle数据库.如何更新选定的列.

问题描述

问题是:

我有两个表(列名放在方括号中): 汽车(CarColorId，CarName)，CarColor(CarColorId，CarColorName)；

I have two tables(column names are in brackets): Cars (CarColorId, CarName), CarColor (CarColorId, CarColorName);

任务是使用字符串"_updated"更新Cars.CarName，但前提是CarColor.CarColorName ='red'.我知道有没有连接的情况下如何做到这一点

The task is to UPDATE Cars.CarName with a string "_updated" but only if CarColor.CarColorName = 'red'. I have know idea how to do this without joins

我已经尝试过这种方式:

I have tried this way:

UPDATE Cars set CarName = concat (CarName, '_updated') WHERE CarColorId = 1;

CarColorId = 1 =红色；该请求有效，但任务是同时使用两个表

CarColorId = 1 = red;This request works, but the task is to use both tables

推荐答案

您可以在Oracle中尝试任何一种

You can try any one of this in Oracle

常规更新

UPDATE
      CARS
SET
      CARS.CARNAME =
          CONCAT ( CARS.CARNAME,
                 '_updated' )
WHERE
      EXISTS
          (SELECT
                CARCOLOR.CARCOLORID
           FROM
                CARCOLOR
           WHERE
                CARS.CARCOLORID = CARCOLOR.CARCOLORID
                AND CARCOLOR.CARCOLORNAME = 'RED');

使用内联视图(如果Oracle认为可更新)

注意:如果您遇到非键保留行错误，请添加索引以解析该索引以使其可更新

Note: If you face a non key preserved row error add an index to resolve the same to make it update-able

UPDATE
      (SELECT
            CARS.CARNAME AS OLD,
            CONCAT ( CARS.CARNAME,
                    '_updated' )
                AS NEW
       FROM
                CARS
            INNER JOIN
                CARCOLOR
            ON CARS.CARCOLORID = CARCOLOR.CARCOLORID
       WHERE
            CARCOLOR.CARCOLORNAME = 'RED') T
SET
      T.OLD     = T.NEW;

使用合并

MERGE INTO
      CARS
USING
      (SELECT
            CARS.ROWID AS RID
       FROM
                CARS
            INNER JOIN
                CARCOLOR
            ON CARS.CARCOLORID = CARCOLOR.CARCOLORID
       WHERE
            CARCOLOR.CARCOLORNAME = 'RED')
ON
      ( ROWID = RID )
WHEN MATCHED
THEN
    UPDATE SET CARS.CARNAME =
                 CONCAT ( CARS.CARNAME,
                        '_updated' );

这篇关于Oracle数据库.如何更新选定的列.的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！