简单的MySQL:

UPDATE users AS U1, users AS U2 
SET U1.name_one = U2.name_colX
WHERE U2.user_id = U1.user_id

UPDATE 
  receipt_invoices dest,
  (
    SELECT 
      `receipt_id`,
      CAST((net * 100) / 112 AS DECIMAL (11, 2)) witoutvat 
    FROM
      receipt 
    WHERE CAST((net * 100) / 112 AS DECIMAL (11, 2)) != total 
      AND vat_percentage = 12
  ) src 
SET
  dest.price = src.witoutvat,
  dest.amount = src.witoutvat 
WHERE col_tobefixed = 1 
  AND dest.`receipt_id` = src.receipt_id ;

希望这将帮助你在一个情况下，你必须匹配和更新两个表。

UPDATE [table_name] AS T1,
      (SELECT [column_name] 
        FROM [table_name] 
        WHERE [column_name] = [value]) AS T2 
  SET T1.[column_name]=T2.[column_name] + 1
WHERE T1.[column_name] = [value];

我有一个重复的条目在一个表本身的问题。以下是对我有效的方法。@sibaz也提倡这样做。

最后我用下面的查询解决了这个问题:

The select query is saved in a temp table IF OBJECT_ID(N'tempdb..#New_format_donor_temp', N'U') IS NOT NULL DROP TABLE #New_format_donor_temp; select * into #New_format_donor_temp from DONOR_EMPLOYMENTS where DONOR_ID IN ( 1, 2 ) -- Test New_format_donor_temp -- SELECT * -- FROM #New_format_donor_temp; The temp table is joined in the update query. UPDATE de SET STATUS_CD=de_new.STATUS_CD, STATUS_REASON_CD=de_new.STATUS_REASON_CD, TYPE_CD=de_new.TYPE_CD FROM DONOR_EMPLOYMENTS AS de INNER JOIN #New_format_donor_temp AS de_new ON de_new.EMP_NO = de.EMP_NO WHERE de.DONOR_ID IN ( 3, 4 )

我不是很有经验的SQL请建议任何更好的方法你知道。

以上查询是针对MySql服务器的。

对于同一张表，

UPDATE PHA_BILL_SEGMENT AS PHA,
     (SELECT BILL_ID, COUNT(REGISTRATION_NUMBER) AS REG 
       FROM PHA_BILL_SEGMENT
        GROUP BY REGISTRATION_NUMBER, BILL_DATE, BILL_AMOUNT
        HAVING REG > 1) T
    SET PHA.BILL_DATE = PHA.BILL_DATE + 2
 WHERE PHA.BILL_ID = T.BILL_ID;

如果您正在从一个复杂的查询进行更新。最好的方法是从查询创建临时表，然后使用临时表作为一个查询进行更新。

    DROP TABLE IF EXISTS cash_sales_sums;
CREATE TEMPORARY TABLE cash_sales_sums as
SELECT tbl_cash_sales_documents.batch_key, COUNT(DISTINCT tbl_cash_sales_documents.cash_sale_number) no_of_docs,
  SUM(tbl_cash_sales_documents.paid_amount) paid_amount, SUM(A.amount - tbl_cash_sales_documents.bonus_amount - tbl_cash_sales_documents.discount_given) amount,
  SUM(A.recs) no_of_entries FROM 
        tbl_cash_sales_documents
    RIGHT JOIN(
        SELECT
            SUM(
                tbl_cash_sales_transactions.amount
            )amount,
            tbl_cash_sales_transactions.cash_sale_document_id,
            COUNT(transaction_id)recs
        FROM
            tbl_cash_sales_transactions
        GROUP BY
            tbl_cash_sales_transactions.cash_sale_document_id
    )A ON A.cash_sale_document_id = tbl_cash_sales_documents.cash_sale_id
    GROUP BY
        tbl_cash_sales_documents.batch_key
ORDER BY batch_key;





UPDATE tbl_cash_sales_batches SET control_totals = (SELECT amount FROM cash_sales_sums WHERE cash_sales_sums.batch_key = tbl_cash_sales_batches.batch_key LIMIT 1),
expected_number_of_documents = (SELECT no_of_docs FROM cash_sales_sums WHERE cash_sales_sums.batch_key = tbl_cash_sales_batches.batch_key),
computer_number_of_documents = expected_number_of_documents,  computer_total_amount = control_totals
WHERE batch_key IN (SELECT batch_key FROM cash_sales_sums);