如何通过相似的值从一个表到另一个表获取值? [英] How can I get values from one table to another via similar values?

问题描述

我有一个名为excel的表，该表具有3列，分别为name，id和full_name. name部分是我仅有的一部分，我需要填写id和full_name.包含数据的另一个表称为tim_pismena，并且有2列我需要的列，id和pismeno_name(实际名称并不重要，但我只是为了清楚起见编写它们).在伪Oracle代码中:)从第二张表中获取值的选择将像这样进行:

I have a table called excel that has 3 columns, name, id, and full_name. The name part is the only one I have and I need to fill id and full_name. The other table that contains the data is called tim_pismena and has 2 columns that I need, id and pismeno_name (the actual names are not important, but i'm writing them just for clarity). In pseudooracle code :) the select that gets me the values from the second table would be done something like this:

SELECT tp.id, tp.pismeno_name
FROM tim_pismena tp
WHERE  upper(tp.pismeno_name) LIKE IN upper('%(SELECT name FROM excel)%')

，当与插入内容一起使用时，最终结果应该类似于

and when used with an insert, the end result should be something like

name        id    full_name
Happy Joe   55    Very fun place Happy Joe, isn't it?

推荐答案

使用合并语句

  1  MERGE
  2     INTO  excel  tgt
  3     USING tim_pismenae src
  4     ON  ( upper(src.naziv_pismena) LIKE '%'||upper(tgt.ime)||'%')
  5  WHEN MATCHED
  6  THEN
  7     UPDATE
  8     SET   tgt.id = src.id
  9     ,     tgt.full_name = src.naziv_pismena
 10  WHEN NOT MATCHED
 11  THEN
 12     INSERT ( tgt.name
 13            , tgt.id
 14            , tgt.full_name )
 15     VALUES ( src.naziv_pismena
 16            , src.id
 17            , src.naziv_pismena )
 18     WHERE (1 <> 1);

这篇关于如何通过相似的值从一个表到另一个表获取值?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！