我正在嘗試做某事....我必須在表格中插入一些資料，但是.....所以這就是我最終的結果...
INSERT into HR.my_employees
(ID,LAST_NAME,FIRST_NAME,userid,SALARY)
SELECT
3 AS ID,
'Biri' AS LAST_NAME,
'Ben' AS FIRST_NAME,
substr(FIRST_NAME,1,1)||''||substr(LAST_NAME,1,7) AS userid,
1100 AS salary
FROM dual
UNION
SELECT
4 AS ID,
'Newman' AS LAST_NAME,
'Chad' AS FIRST_NAME,
substr(FIRST_NAME,1,1)||''||substr(LAST_NAME,1,7) AS userid,
750 AS salary
FROM dual;
任何建議...
uj5u.com熱心網友回復：
您不能在定義別名的子查詢的SELECTor子句中參考別名。WHERE在子查詢（或子查詢因式分解子句）中生成資料，然后在外部查詢中參考它：
INSERT into HR.my_employees(ID,LAST_NAME,FIRST_NAME,userid,SALARY)
WITH data (id, last_name, first_name, salary) AS (
SELECT 3, 'Biri', 'Ben', 1100 FROM DUAL UNION ALL
SELECT 4, 'Newman', 'Chad', 750 FROM DUAL
)
SELECT id,
last_name,
first_name,
SUBSTR(first_name,1,1) || SUBSTR(last_name,1,7),
salary
FROM data;
或者：
INSERT into HR.my_employees(ID,LAST_NAME,FIRST_NAME,userid,SALARY)
SELECT id,
last_name,
first_name,
SUBSTR(first_name,1,1) || SUBSTR(last_name,1,7),
salary
FROM (
SELECT 3 AS id, 'Biri' AS last_name, 'Ben' AS first_name, 1100 AS salary FROM DUAL
UNION ALL
SELECT 4, 'Newman', 'Chad', 750 FROM DUAL
);
小提琴
uj5u.com熱心網友回復：
為什么要使用 select 陳述句？- 根據您提供的值，您似乎想要插入硬編碼值。或者雙表是否包含您要插入 HR.my_employees 的資料？
如果您只想將值插入表中，而不是從現有的表中插入，則可以使用以下結構：
INSERT INTO table_name (column1, column2, column3, ..., columnXX)
VALUES (value1, value2, value3, ..., valueXX);
轉載請註明出處，本文鏈接：https://www.uj5u.com/yidong/529792.html