顺子の杂货铺
生命不息，折腾不止，且行且珍惜~
        找回密码
当前位置：順子の杂货铺 > BigData > 正文

20-关联更新与删除

2026-01-18 分类：BigData / BLOG / Database / SQL 阅读(3) 评论(0)
DMIT VPS

关联更新与删除

关联更新和删除是数据维护中的常见操作，本篇介绍各数据库的实现方式。

📊 支持情况

数据库UPDATE JOINDELETE JOINMERGE备注
MySQL✅✅❌支持直接JOIN
Oracle✅✅✅MERGE功能强大
ClickHouse✅✅–使用ALTER
Hologres✅✅–兼容PG
MaxCompute✅✅–INSERT OVERWRITE
Hive✅✅–UPDATE/DELETE 2.0+

1. 关联更新

MySQL 多表UPDATE

-- 方式1：JOIN语法
UPDATE orders o
JOIN customers c ON o.user_id = c.user_id
SET o.discount = c.level_discount,
    o.total_amount = o.subtotal * (1 - c.level_discount)
WHERE o.status = 'pending';

-- 方式2：子查询
UPDATE orders
SET discount = (
    SELECT level_discount
    FROM customers
    WHERE customers.user_id = orders.user_id
)
WHERE status = 'pending';

Oracle 多表UPDATE

-- 方式1：子查询
UPDATE orders o
SET (discount, total_amount) = (
    SELECT c.level_discount,
           o.subtotal * (1 - c.level_discount)
    FROM customers c
    WHERE c.user_id = o.user_id
)
WHERE EXISTS (
    SELECT 1 FROM customers c WHERE c.user_id = o.user_id
);

-- 方式2：MERGE
MERGE INTO orders o
USING customers c
ON (o.user_id = c.user_id AND o.status = 'pending')
WHEN MATCHED THEN
    SET discount = c.level_discount,
        total_amount = subtotal * (1 - c.level_discount);

ClickHouse

-- ClickHouse使用ALTER UPDATE
ALTER TABLE orders
UPDATE discount = (SELECT level_discount FROM customers WHERE customers.user_id = orders.user_id)
WHERE status = 'pending';

Hive / MaxCompute

-- Hive 2.0+ 支持UPDATE
UPDATE orders
SET discount = (
    SELECT level_discount FROM customers WHERE customers.user_id = orders.user_id
)
WHERE status = 'pending';

-- MaxCompute使用INSERT OVERWRITE
INSERT OVERWRITE TABLE orders
SELECT
    o.order_id,
    o.user_id,
    COALESCE(c.level_discount, 0) AS discount,
    ...
FROM orders o
LEFT JOIN customers c ON o.user_id = c.user_id;

2. 关联删除

MySQL 多表DELETE

-- 删除关联数据
DELETE o FROM orders o
JOIN customers c ON o.user_id = c.user_id
WHERE c.status = 'inactive';

-- 删除在另一个表中存在的记录
DELETE FROM orders
WHERE user_id IN (SELECT user_id FROM blacklist_users);

-- 使用JOIN删除
DELETE o
FROM orders o
INNER JOIN products p ON o.product_id = p.product_id
WHERE p.status = 'discontinued';

Oracle 多表DELETE

-- 使用子查询
DELETE FROM orders
WHERE user_id IN (
    SELECT user_id FROM customers WHERE status = 'inactive'
);

-- 使用EXISTS
DELETE FROM orders o
WHERE EXISTS (
    SELECT 1 FROM customers c
    WHERE c.user_id = o.user_id AND c.status = 'inactive'
);

ClickHouse

-- ALTER DELETE
ALTER TABLE orders
DELETE WHERE user_id IN (SELECT user_id FROM blacklist_users);

Hive / MaxCompute

-- Hive
DELETE FROM orders
WHERE user_id IN (SELECT user_id FROM blacklist_users);

-- MaxCompute
INSERT OVERWRITE TABLE orders
SELECT * FROM orders
WHERE user_id NOT IN (SELECT user_id FROM blacklist_users);

3. MERGE 语句

Oracle MERGE

MERGE INTO target_table t
USING source_table s
ON (t.id = s.id)
WHEN MATCHED THEN
    UPDATE SET
        t.name = s.name,
        t.value = s.value
    WHERE t.status = 'active'
WHEN NOT MATCHED THEN
    INSERT (id, name, value, create_time)
    VALUES (s.id, s.name, s.value, SYSDATE);

条件MERGE

MERGE INTO orders o
USING order_updates u
ON (o.order_id = u.order_id)
WHEN MATCHED AND o.status = 'pending' THEN
    UPDATE SET o.status = u.new_status, o.amount = u.amount
WHEN MATCHED AND o.status = 'cancelled' THEN
    DELETE
WHEN NOT MATCHED THEN
    INSERT (order_id, user_id, amount, status)
    VALUES (u.order_id, u.user_id, u.amount, 'new');

4. 实战案例

场景1：同步客户等级

-- MySQL - 根据最新等级更新订单折扣
UPDATE orders o
JOIN (
    SELECT user_id, level, level_discount
    FROM customers
    WHERE status = 'active'
) c ON o.user_id = c.user_id
SET o.discount = c.level_discount,
    o.total_amount = ROUND(o.subtotal * (1 - c.level_discount), 2),
    o.updated_at = NOW()
WHERE o.status = 'pending';

场景2：清理无效数据

-- MySQL - 删除无订单的客户
DELETE c FROM customers c
LEFT JOIN orders o ON c.user_id = o.user_id
WHERE o.order_id IS NULL
  AND c.created_at < DATE_SUB(CURDATE(), INTERVAL 1 YEAR);

场景3：批量更新状态

-- Oracle - 批量更新订单状态
MERGE INTO orders o
USING (
    SELECT order_id, 'completed' AS new_status
    FROM orders
    WHERE status = 'processing'
      AND payment_time IS NOT NULL
) u
ON (o.order_id = u.order_id)
WHEN MATCHED THEN
    UPDATE SET
        o.status = u.new_status,
        o.completed_at = SYSDATE
WHERE o.status = 'processing';

场景4：价格同步

-- MaxCompute - 价格表同步
INSERT OVERWRITE TABLE product_prices
SELECT
    p.product_id,
    p.product_name,
    COALESCE(pp.price, p.base_price) AS price,
    COALESCE(pp.discount_price, p.base_discount_price) AS discount_price,
    COALESCE(pp.updated_at, CURRENT_TIMESTAMP) AS updated_at
FROM products p
LEFT JOIN product_prices_update pp ON p.product_id = pp.product_id
WHERE pp.product_id IS NOT NULL
   OR p.updated_at > (SELECT MAX(updated_at) FROM product_prices);

5. 事务与并发

事务中更新

-- MySQL
START TRANSACTION;

UPDATE accounts SET balance = balance - 100 WHERE user_id = 1;
UPDATE accounts SET balance = balance + 100 WHERE user_id = 2;

-- 检查约束
SELECT * FROM accounts WHERE user_id IN (1, 2);
COMMIT;

-- 回滚
ROLLBACK;

锁等待处理

-- Oracle - 使用NOWAIT避免长时间等待
SELECT * FROM orders
WHERE order_id = 1001
FOR UPDATE NOWAIT;

-- 使用SKIP LOCKED
SELECT * FROM orders
WHERE status = 'pending'
FOR UPDATE SKIP LOCKED;

6. 性能优化

批量操作

-- ✅ 建议：批量更新
UPDATE orders
SET status = 'completed'
WHERE order_id IN (1, 2, 3, 4, 5);

-- ✅ 建议：分批删除大量数据
DELETE FROM logs
WHERE created_at < DATE_SUB(CURDATE(), INTERVAL 1 YEAR)
LIMIT 10000;  -- 分批删除

索引优化

-- 确保关联字段有索引
CREATE INDEX idx_orders_user_id ON orders(user_id);
CREATE INDEX idx_orders_status ON orders(status);

⚠️ 注意事项

1. 影响行数

-- 查看影响的行数
SELECT ROW_COUNT();  -- MySQL

-- Oracle自动返回匹配行数

2. 级联更新

-- 注意触发器影响
-- MySQL
SHOW TRIGGERS FROM database_name;

3. 权限要求

-- 需要UPDATE/DELETE权限
GRANT UPDATE, DELETE ON orders TO user_name;

📝 练习题

-- 建表
CREATE TABLE customers (
    user_id INT,
    name STRING,
    level STRING,
    level_discount DECIMAL(4,2),
    status STRING,
    updated_at DATETIME
);

CREATE TABLE orders (
    order_id INT,
    user_id INT,
    subtotal DECIMAL(10,2),
    discount DECIMAL(4,2),
    total_amount DECIMAL(10,2),
    status STRING,
    updated_at DATETIME
);

INSERT INTO customers VALUES
(1, '张三', 'VIP', 0.10, 'active', '2024-01-14'),
(2, '李四', 'GOLD', 0.08, 'active', '2024-01-14'),
(3, '王五', 'NORMAL', 0.00, 'inactive', '2024-01-14');

INSERT INTO orders VALUES
(1, 1, 1000, 0.00, 1000, 'pending', '2024-01-14'),
(2, 2, 500, 0.00, 500, 'pending', '2024-01-14'),
(3, 3, 800, 0.00, 800, 'pending', '2024-01-14');

要求：

  1. 根据客户等级更新订单折扣和总金额
  2. 删除非活跃客户的订单
  3. 使用MERGE同步客户信息
  4. 将已完成的订单折扣设置为0
-- 参考答案（MySQL）
-- 1. 更新订单折扣和金额
UPDATE orders o
JOIN customers c ON o.user_id = c.user_id
SET
    o.discount = c.level_discount,
    o.total_amount = ROUND(o.subtotal * (1 - c.level_discount), 2),
    o.updated_at = NOW()
WHERE o.status = 'pending';

-- 2. 删除非活跃客户订单
DELETE o FROM orders o
JOIN customers c ON o.user_id = c.user_id
WHERE c.status = 'inactive';

-- 3. MERGE同步（Oracle）
MERGE INTO orders o
USING customers c
ON (o.user_id = c.user_id AND o.status = 'pending')
WHEN MATCHED THEN
    UPDATE SET
        o.discount = c.level_discount,
        o.total_amount = ROUND(o.subtotal * (1 - c.level_discount), 2);

-- 4. 完成订单折扣归零
UPDATE orders
SET discount = 0.00, updated_at = NOW()
WHERE status = 'completed';
赞(0)
未经允许不得转载：順子の杂货铺 » 20-关联更新与删除
标签：
Avatar photo

shunzi

一半忧伤，一半明媚！喜欢看书，听歌，写字，打游戏，交朋友！
搬瓦工VPS

相关推荐

评论 抢沙发

Avatar photo

取消

  • 昵称 (必填)
  • 邮箱 (必填)
  • 网址
吐血推荐

New

热门文章

热门标签

sql (48)mysql (22)oracle (21)python (15)Hive (13)MaxCompute (13)Hologres (13)ClickHouse (12)rm (6)password (5)port (5)install (4)linux (4)log (4)join (3)pos (3)google (3)vi (3)tab (3)redis (3)deepin (3)htaccess (2)master (2)centos (2)kangle (2)easypanel (2)list (2)django (2)plsql (2)docker (2)

网站统计

  • 日志总数：88
  • 评论总数：55
  • 标签总数：125
  • 最后更新：2026-01-18

分享创造快乐

联系我们联系我们