时间戳

首页 > 解决方案 > SQL INSERT INTO 请帮忙

sql - SQL INSERT INTO 请帮忙

问题描述

好的，所以我觉得我错过了一些如此微不足道的东西，但我不知道我做错了什么。我只是想将一些数据插入到表中，但出现错误

INSERT INTO SHOPS
VALUES
('12345', '10 smith street', 'brunswick', '3030', 
'0404123123', 'shop1@shop.com'**),**
('65435', '10 road street', 'fitzroy', '3030', 
'0404546564', 'shop2@shop.com'),
('34232', '10 car street', 'carlton', '3030', '0404455432', 
'shop3@shop.com');

所以我在括号和逗号上遇到错误，我在第 4 行加粗。

如果有人知道如何解决这个问题，我将不胜感激。

标签： sqloracle

解决方案

如果看起来您正在使用 MySQL 格式进行多行 INSERT 语句。这不适用于 Oracle。它应该是

INSERT ALL
  INTO SHOPS  (FIELD1, FIELD2, FIELD3, FIELD4, FIELD5, FIELD6)
       VALUES ('12345', '10 smith street', 'brunswick', '3030', '0404123123', 
               'shop1@shop.com'),
  INTO SHOPS  (FIELD1, FIELD2, FIELD3, FIELD4, FIELD5, FIELD6)
       VALUES ('65435', '10 road street', 'fitzroy', '3030', '0404546564', 
               'shop2@shop.com'),
  INTO SHOPS  (FIELD1, FIELD2, FIELD3, FIELD4, FIELD5, FIELD6)
       VALUES ('34232', '10 car street', 'carlton', '3030', '0404455432', 
               'shop3@shop.com')
SELECT * FROM DUAL;

作为替代方案，您可以使用不同形式的 INSERT 语句：

INSERT INTO SHOPS (FIELD1, FIELD2, FIELD3, FIELD4, FIELD5, FIELD6)
  SELECT '12345', '10 smith street', 'brunswick', '3030', '0404123123', 
               'shop1@shop.com' FROM DUAL UNION ALL
  SELECT '65435', '10 road street', 'fitzroy', '3030', '0404546564', 
               'shop2@shop.com' FROM DUAL UNION ALL
  SELECT '34232', '10 car street', 'carlton', '3030', '0404455432', 
               'shop3@shop.com' FROM DUAL

或者您可以使用 MERGE 语句：

MERGE INTO SHOPS s
  USING (SELECT '12345' AS FIELD1, '10 smith street' AS FIELD2, 'brunswick' AS FIELD3,
                '3030' AS FIELD4, '0404123123' AS FIELD5,
                'shop1@shop.com' AS FIELD6 FROM DUAL UNION ALL
         SELECT '65435', '10 road street', 'fitzroy', '3030', '0404546564', 
               'shop2@shop.com' FROM DUAL UNION ALL
         SELECT '34232', '10 car street', 'carlton', '3030', '0404455432', 
               'shop3@shop.com' FROM DUAL) d
     ON (d.FIELD1 = s.FIELD1)
  WHEN NOT MATCHED THEN
    INSERT (FIELD1, FIELD2, FIELD3, FIELD4, FIELD5, FIELD6)
    VALUES (d.FIELD1, d.FIELD2, d.FIELD3, d.FIELD4, d.FIELD5, d.FIELD6);

我使用了 FIELD1、FIELD2、...、FIELD6，因为您的 INSERT 语句中没有包含字段列表 - 将它们替换为正确的字段名称。MERGE 语句假定 FIELD1 是 SHOPS 上的主键 - 将其替换为正确的字段名称。

推荐阅读