sql 的 update操作_update sql用法 csdn

sqlserver:在SQL Server中，你的UPDATE语句的语法需要稍微调整一下。你不能直接在SET子句之后立即跟INNER JOIN。正确的语法是将JOIN操作放在UPDATE和SET之间。以下是正确的写法：

UPDATE test_item
SET test_item.s1no = subcontractor.s1no
FROM test_item
INNER JOIN subcontractor ON test_item.s1code = subcontractor.s1code;

如果使用子查询

WITH CTE AS (
    SELECT t1.no, t1.SUBCONTRACTOR_NO, t2.subcontractor_code, t1.TEST_ITEM_ID,
           t3.test_item_code, t4.id
    FROM SUBCONTRACT t1
    LEFT JOIN SUBCONTRACTOR t2 ON t1.SUBCONTRACTOR_NO = t2.no
    LEFT JOIN TEST_ITEM t3 ON t1.TEST_ITEM_ID = t3.id
    LEFT JOIN TEST_ITEM t4 ON t2.SUBCONTRACTOR_code = t4.subcontractor_code AND t3.TEST_ITEM_code = t4.test_item_code
    WHERE t1.SUBCONTRACTOR_NO >= 1100 
)

UPDATE t1
SET t1.TEST_ITEM_ID = cte.id
FROM SUBCONTRACT t1
INNER JOIN CTE cte ON t1.no = cte.no;

mysql:在MySQL中，标准的UPDATE语句不直接支持FROM子句，但你可以通过使用INNER JOIN或JOIN来达到类似的效果，以此来更新表中的记录，基于另一张表的数据。以下是MySQL中类似的语法示例：

UPDATE orders o
INNER JOIN order_updates ou
ON o.order_id = ou.order_id
SET o.status = ou.new_status;

oracle:在Oracle数据库中，标准的UPDATE语句同样不直接包含FROM子句，但你可以使用子查询或者MERGE语句来达到类似于其他数据库中通过FROM子句更新数据的效果。

UPDATE table_to_update t1
SET (column_name1, column_name2) = 
    (SELECT column_value1, column_value2
     FROM reference_table t2
     WHERE t1.matching_column = t2.matching_column)
WHERE EXISTS (
    SELECT 1
    FROM reference_table t2
    WHERE t1.matching_column = t2.matching_column);

select  
  t1.NO,
  t1.APPLICANT_NO,
    FORMAT(t1.APPLY_DATE, 'yyyy/MM/dd', 'en-US') as APPLY_DATE,
    t1.JOB_NO,
    t1.PRODUCT,
    t1.MODEL,
    FORMAT(t1.TARGET_DATE, 'yyyy/MM/dd', 'en-US') as TARGET_DATE,
    t1.CATEGORY,
    t1.SUBCONTRACTOR_NO,
    t2.CHN_NAME as SubcontractorCHNName,
    t2.subcontractor_code,
    t1.TEST_ITEM_ID,
    ISNULL(t3.CATEGORY, 0)as TestItemCategory,
    t3.DESCRIPTION as TestItemName,
    t3.test_item_code,
    t1.TEST_FEE_CURRENCY_NAME,
    t1.TEST_FEE_EXCHANGE_RATE,
    t1.TEST_FEE_AMOUNT,
    t1.COMMENTS,
    CASE t1.Status
    WHEN 2 THEN 'Created'
    WHEN 20 THEN 'Approved'
    WHEN 5 THEN 'Confirmed'
END
Status,
                 (SELECT max(DIRTY_FLAG) from SUBCONTRACT_TRACK where no=t1.no and STATUS=20 GROUP BY no) as APPROVE_DATE,
                 t4.Service_Amount*t4.Service_Exch_rate as JobInAmount,
                 t1.TEST_FEE_CURRENCY_NAME as '币种'
    from subcontract t1
     left join SUBCONTRACTOR t2 on t1.SUBCONTRACTOR_NO=t2.no
    left join test_item t3 on t1.TEST_ITEM_ID = t3.id
left join job t4 on t1.JOB_NO=t4.JOB_NO
    where t1.status in (5, 20)