How to update values from multiple row table to another multiple table in SQL Server [duplicate]

ukdjmx9f 于 2024-01-05 发布在 SQL Server

关注(0)|答案(2)|浏览(161)

This question already has answers here:

SQL update query using joins (13 answers)
Closed last month.

I have two tables with multiple records in and I want to update one table's rows with a value from another tables records matched on ORD_NAME and OP_NAME .

My SQL so far:

INSERT INTO OPS_NOTREQUIRED (ORD_NAME, OP_NAME, STATUS)
VALUES ('1', '10', 'DELETE')
INSERT INTO OPS_NOTREQUIRED (ORD_NAME, OP_NAME, STATUS)
VALUES ('2', '10', 'DELETE')
INSERT INTO OPS_NOTREQUIRED (ORD_NAME, OP_NAME, STATUS)
VALUES ('3', '10', '')

INSERT INTO IMPORT (ORD_NAME, ORD_QTY, ORD_PART, ORD_PARTREV, ORD_DUEDATE, OP_NAME, EAI_Status)
VALUES ('1', '400', '1234', '1', '2023-12-01 09:10:04.530', '10', 'NULL')
INSERT INTO IMPORT (ORD_NAME, ORD_QTY, ORD_PART, ORD_PARTREV, ORD_DUEDATE, OP_NAME, EAI_Status)
VALUES ('2', '400', '1234', '1', '2023-12-01 09:10:04.530', '10', 'NULL')
INSERT INTO IMPORT (ORD_NAME, ORD_QTY, ORD_PART, ORD_PARTREV, ORD_DUEDATE, OP_NAME, EAI_Status)
VALUES ('3', '400', '1234', '1', '2023-12-01 09:10:04.530', '10', 'NULL')


UPDATE [IMPORT] 
SET EAI_Status = 'DELETE' 
WHERE (SELECT EAI_Status FROM OPS_NOTREQUIRED) = 'DELETE'

I want the IMPORT table to have the same values as the OPS_NOTREQUIRED table for column STATUS , but I can't seem to figure it out.

sql-server

来源：https://stackoverflow.com/questions/77586791/how-to-update-values-from-multiple-row-table-to-another-multiple-table-in-sql-se

2条答案

按热度按时间

ncgqoxb01#

I found the merge command.

MERGE INTO IMPORT
USING OPS_NOTREQUIRED
ON IMPORT.ORD_NAME = OPS_NOTREQUIRED.ORD_NAME
AND IMPORT.OP_NAME = OPS_NOTREQUIRED.OP_NAME
WHEN MATCHED THEN
UPDATE SET EAI_Status = OPS_NOTREQUIRED.STATUS;

赞(0）回复(0）举报 2024-01-05

sxissh062#

This can be done using a regular inner join in an update statement:

UPDATE I
SET EAI_Status = O.STATUS
FROM OPS_NOTREQUIRED O
JOIN IMPORT I
    ON I.ORD_NAME = O.ORD_NAME
    AND I.OP_NAME = O.OP_NAME

Note the use of aliases that allow the UPDATE clause to conveniently reference the target table already referenced in the FROM clause.

赞(0）回复(0）举报 2024-01-05

我来回答

How to update values from multiple row table to another multiple table in SQL Server [duplicate]

2条答案

相关问题

热门标签

最新问答