SQL Query error

The value returned by your subquery needs to be a single value.  You're trying to set SequenceNo equal to a recordset.

If you're trying to correct the SequenceNo value in SalesOrderTemp with the value in vSO_SalesOrderHistoryDetail and SalesOrderNo + LineKey represents a primary or natural key on the table then you can use something like...

UPDATE SOTemp
SET SequenceNo = SOHD.SequenceNo
FROM SalesOrderTemp AS SOTemp
INNER JOIN vSO_SalesOrderHistoryDetail AS SOHD
    ON SOTemp.SONo = SOHD.SalesOrderNo
    AND SOTemp.LineKey = SOHD.LineKey

Select allOpen in new window

Obviously this with limited knowledge of your schema so please don't run without confirming.

According to your data model, a order position can move, thus have different sequence numbers over time.
Thus you need to tell your sub-query, what number you want. The first, the last or any other.

E.g. for the last date:

UPDATE  O
SET     O.SequenceNo = (
    SELECT  TOP 1 D.SequenceNo
    FROM    vSO_SalesOrderHistoryDetail D
    WHEREO  O.SONo = D.SalesOrderNo
        AND O.LineKey = D.LineKey
    ORDER BY D.SomeDateColumn DESC
    )
FROM    SalesOrderTemp O;

Select allOpen in new window

May be I put a bad description.
Here attached the select results from my two tables.
I'd like to have in t1 the same SequenceNo that I have in t2

As you see in line 6 and 7, there are two matches with your join predicates (SalesOrderNo, LineKey). Thus you need to tell the sub-query which you want.

This order criteria can be anything you want to be, as long as it is in the date. Like an ORDER BY a date or the sequence number itself. E.g. the the maximum sequence number it had in the past:

UPDATE  O
SET     O.SequenceNo = (
    SELECT  TOP 1 D.SequenceNo
    FROM    vSO_SalesOrderHistoryDetail D
    WHEREO  O.SONo = D.SalesOrderNo
        AND O.LineKey = D.LineKey
    ORDER BY D.SequenceNo DESC
    )
FROM    SalesOrderTemp O;

Select allOpen in new window

This is the results of your query:

Well, without knowing your model, these are guesses.

Consider posting a concise and complete example. Include table DDL - preferably as table variables - and sample data INSERT statements as single, runable T-SQL script.

I'm sorry, I'm not sure to well understood what you mean. My knowledge of T-SQL is pretty poor.
What I can say is that my t1 (SalesOrderTemp) is not a temporary table. It contains the records coming from another database (linked database). Now, I have to add a new field (SequenceNo) and I need to updated it.

Thanks in advance for your help.

D'oh?

Post the table structure. And data. So that we can post adequate solutions. And I did say use table variables, cause this means no disk allocation is needed on our systems for testing. Something like

-- Setup.
-- IMPORTANT: Your primary key and the candidate keys MUST exist.
DECLARE @SalesOrderTemp TABLE (
    SequenceNo INT ,
    SONo INT ,
    LineKey INT
);
DECLARE @vSO_SalesOrderHistoryDetail TABLE (
    SequenceNo INT ,
    SalesOrderNo INT ,
    LineKey INT
);

-- Sample data.
--INSERT INTO @SalesOrderTemp () VALUES (),(),();
--INSERT INTO @vSO_SalesOrderHistoryDetail () VALUES (),(),();

-- Modify data.
UPDATE O
SET    O.SequenceNo = (   SELECT   TOP 1 D.SequenceNo
                          FROM     @vSO_SalesOrderHistoryDetail D
                          WHERE    O.SONo = D.SalesOrderNo
                                   AND O.LineKey = D.LineKey
                          ORDER BY D.SequenceNo DESC )
FROM   @SalesOrderTemp O;

-- Final output.
SELECT *
FROM   @SalesOrderTemp SOT;

SELECT *
FROM   @vSO_SalesOrderHistoryDetail;

Select allOpen in new window

btw,T-SQL is implementation detail. What do you know about database theory, normalization and in your case about ETL processing? You should start by carefully designing and documenting  the process. This includes looking at each step and documenting what needs to be done, what pre- and post-conditions must be satisfied.

So maybe you should also rephrase your question and give us more context. What are you exactly doing? What is the business case, what entitles are involved?

-- SETUP
CREATE TABLE [dbo].[SalesOrderTemp](
   [ID_SO] [int] IDENTITY(1,1) NOT NULL,
   [SONo] [nchar](10) NOT NULL,
   [OrderType] [nchar](10) NULL,
   [LineKey] [nchar](10) NULL,
   [SequenceNo] [nchar](14) NULL,
   [SerialNo] [nchar](25) NULL,
   [EventNo] [nchar](10) NULL,

 CONSTRAINT [PK_SalesOrderTemp] PRIMARY KEY CLUSTERED
(
   [ID_SO] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

-- INSERT DATA
INSERT INTO SalesOrderTemp
                      (SONo, OrderType, OrderStatus, CancelledLine, LineKey, SerialNo, EventNo)
SELECT         vSO_SalesOrderHistoryHeader.SalesOrderNo, vSO_SalesOrderHistoryHeader.UDF_ORDER_TYPE,                             vSO_SalesOrderHistoryHeader.OrderStatus, vSO_SalesOrderHistoryDetail.CancelledLine,
                       vSO_SalesOrderHistoryDetail.LineKey, vSO_SalesOrderHistoryDetail.UDF_SERIAL,                                                       vSO_SalesOrderHistoryDetail.UDF_EVENT
FROM            vSO_SalesOrderHistoryDetail INNER JOIN
                       vSO_SalesOrderHistoryHeader ON vSO_SalesOrderHistoryDetail.SalesOrderNo =                                                           vSO_SalesOrderHistoryHeader.SalesOrderNo  
WHERE        (SalesOrderTemp_1.SONo IS NULL) AND (SalesOrderTemp_1.LineKey IS NULL)

Now I added the new field in SalesOrderTemp (SequenceNo) and I'd like to update it with the existing one in  vSO_SalesOrderHistoryDetail. The vSO_SalesOrderHistoryDetail is a view from another server/database (linked Server)

Hope this helps.
Thanks

The point is, that we have a working script with meaningful data. Thus you must provide INSERT ..VALUES .. data statements. Not INSERT .. SELECT. We don't have your database at hands.

And for your example:
You must explain what you're trying to do.
Cause ID_SO being the only candidate key is wrong under most circumstances in an OLAP model.

INSERT INTO SalesOrderTemp
                  (SONo, OrderType, OrderStatus, CancelledLine, LineKey)
VALUES      ('0011139', 'Service', '010 - order entry', 'N', '000001'),
                    ('0011139', 'Service', '010 - order entry', 'Y', '000002'),
                    ('0011139', 'Service', '010 - order entry', 'N', '000003'),
                    ('0011139', 'Service', '010 - order entry', 'N', '000004'),
                    ('0011139', 'Service', '010 - order entry', 'Y', '000005'),
                    ('0011139', 'Service', '010 - order entry', 'Y', '000006'),
                    ('0011139', 'Service', '010 - order entry', 'N', '000006')

The keys could be SoNo and SequenceNo

The keys are the problem.

In your sample:

('0011139', 'Service', '010 - order entry', 'Y', '000006'),
('0011139', 'Service', '010 - order entry', 'N', '000006')

Select allOpen in new window

Those two rows are not distinguishable. Also the model is problematic.

SONo is the sales order number identifying the sales order? Then OrderType, OrderStatus are functional depended.
The rest, CancelledLine is a state, functional depended of LineKey..

(SONo, LineKey) as only key is not unique.

This is the problem you  see in your outcome after applying my solution.
In general, we must know what the keys, all candidates keys, are to help you in this scenario. Otherwise we cannot even guess.

Well, in this case I can guess.

(CancelledLine, LineKey) and your "history" in the view name indicate that there must be further columns to order the rows according to their change date. Cause this is the normal scenario.

Thus your INSERT data being usable, should look like

INSERT INTO SalesOrderTemp ( SONo ,
                             OrderType ,
                             OrderStatus ,
                             CancelledLine ,
                             LineKey ,
                             ChangeDate )
VALUES ( '0011139', 'Service', '010 - order entry', 'N', '000001', '2021-05-01' ) ,
       ( '0011139', 'Service', '010 - order entry', 'Y', '000002', '2021-05-01' ) ,
       ( '0011139', 'Service', '010 - order entry', 'N', '000003', '2021-05-01' ) ,
       ( '0011139', 'Service', '010 - order entry', 'N', '000004', '2021-05-01' ) ,
       ( '0011139', 'Service', '010 - order entry', 'Y', '000005', '2021-05-01' ) ,
       ( '0011139', 'Service', '010 - order entry', 'Y', '000006', '2021-05-01' ) ,
       ( '0011139', 'Service', '010 - order entry', 'N', '000006', '2021-05-02' );

Select allOpen in new window

Now we could pick the correct one based on the date. Cause the relevant candidate key for your problem is (SONo, LineKey, ChangeDate).

I hope you see, why and what kind of information is needed to help you.

Yes, SONo is the sales order number identifying the sales order.
Yes, I have a field "DateUpdated" and/or "TimeUpdated" that I could use.