×

Loading...
Ad by
  • 推荐 OXIO 加拿大高速网络,最低月费仅$40. 使用推荐码 RCR37MB 可获得一个月的免费服务
Ad by
  • 推荐 OXIO 加拿大高速网络,最低月费仅$40. 使用推荐码 RCR37MB 可获得一个月的免费服务

what kind of database are u using?

what kind of database are u using? For oracle, I know you can do that use "merge" statement in oracle 9i: example
MERGE INTO sales s
USING new_sales n
ON (s.sales_transaction_id = n.sales_transaction_id)
WHEN MATCHED THEN
UPDATE s_quantity = s_quantity + n_quantity, s_dollar = s_dollar + n_dollar
WHEN NOT MATCHED THEN
INSERT (sales_quantity_sold, sales_dollar_amount)
VALUES (n.sales_quantity_sold, n.sales_dollar_amount);

for oracle version prior to 9i, u have to use two sql statement like:
UPDATE
(SELECT
s.sales_quantity_sold AS s_quantity,
s.sales_dollar_amount AS s_dollar,
n.sales_quantity_sold AS n_quantity,
n.sales_dollar_amount AS n_dollar
FROM sales s, new_sales n
WHERE s.sales_transaction_id = n.sales_transaction_id) sales_view
SET s_quantity = s_quantity + n_quantity, s_dollar = s_dollar + n_dollar;
INSERT INTO sales
SELECT * FROM new_sales s
WHERE NOT EXISTS
(SELECT 'x' FROM FROM sales t
WHERE s.sales_transaction_id = t.sales_transaction_id);
Report

Replies, comments and Discussions:

  • 工作学习 / IT技术讨论 / 有没有INSERT 和UPDATE 一起使用的SQL 语句.如果记录某个KEY VALUE 存在表里那么就UPDATE ,否则就INSERT.
    • While exists Blabla... Begin.....update....End while not exsites Blabla Begin.......insert ....End
      • 还得写两条SQL 语句.麻烦!
        • 这就嫌麻烦了。
          • 要考虑到执行的效率.有一千条记录存在内存里,每次取出一条,判断该条记录的KEY 是否存在表里 .如果存在 执行UPDATE 否则 INSERT.这样每条记录要访问两次表,我不清楚有什么更好的办法.
            • 这个方法会好一点点
              T-SQL:
              UPDATE aTable SET aField = newValue WHERE aKey = keyValue
              IF @@ROWCOUNT = 0
              BEGIN
              INSERT INTO aTable (...) VALUES (...)
              END
            • 这个和"还得写两条SQL 语句.麻烦!"有什么关系?
    • what kind of database are u using?
      what kind of database are u using? For oracle, I know you can do that use "merge" statement in oracle 9i: example
      MERGE INTO sales s
      USING new_sales n
      ON (s.sales_transaction_id = n.sales_transaction_id)
      WHEN MATCHED THEN
      UPDATE s_quantity = s_quantity + n_quantity, s_dollar = s_dollar + n_dollar
      WHEN NOT MATCHED THEN
      INSERT (sales_quantity_sold, sales_dollar_amount)
      VALUES (n.sales_quantity_sold, n.sales_dollar_amount);

      for oracle version prior to 9i, u have to use two sql statement like:
      UPDATE
      (SELECT
      s.sales_quantity_sold AS s_quantity,
      s.sales_dollar_amount AS s_dollar,
      n.sales_quantity_sold AS n_quantity,
      n.sales_dollar_amount AS n_dollar
      FROM sales s, new_sales n
      WHERE s.sales_transaction_id = n.sales_transaction_id) sales_view
      SET s_quantity = s_quantity + n_quantity, s_dollar = s_dollar + n_dollar;
      INSERT INTO sales
      SELECT * FROM new_sales s
      WHERE NOT EXISTS
      (SELECT 'x' FROM FROM sales t
      WHERE s.sales_transaction_id = t.sales_transaction_id);
    • oracle9i upsert
    • oracle 9i merge
    • If using DB2 you can do "INSERT_UPDATE INTO...."
      • if DB2, use MERGE
    • 这种需求是如何产生的呢?
      • 比如买了新的股票那么是INSERT, 如果把手中现有的股票抛出去一部分,那么是update
        我只在程序开始运行和要程序运行结束的时候才和数据库打交道的.
        • 如果你真是这个需求, 那没问题.程序开始时, 把持有的股票调入内存, 结束时, 对于卖出的, 无疑是UPDATE, 对于买入的, 只需和内存中有限的记录比较,然后决定用UPDATE或者INSERT.
        • 如果这样你要check的应该是buy/sell. 如果是buy, 就应该是insert, 不要check database; 如果是sell, check 是不是有足够的share to sell
          奇怪,股票交易里怎么可能作update呢, database里的数据只能insert, 不能有update 和 delete的啊