Home / Questions / 319485
Accepted
Ivxn
Asked: 2020-01-03 16:33:34 +0800 CST 2020-01-03 16:33:34 +0800 CST

Update with multiple values ​​in SQL Server

  • 772

How can I do an update so that it updates several values, my query is

    UPDATE ACL_FAC SET ID_CARGA = 
(SELECT SEQUENCIA FROM ACL_FAC WHERE ID_CARGA IN (4005946,
4005947,
4005948,
4005949,
4005950)AND SEQUENCIA IN(680456522,
680482070,
680561046,
680561049,
680562329)
) 
WHERE ID_CARGA IN (4005946,
4005947,
4005948,
4005949,
4005950)AND SEQUENCIA IN(680456522,
680482070,
680561046,
680561049,
680562329)

what I want to do is update the ID_CARGA field placing the value that has SEQUENCE but this is for several values, with one value if possible but with several values ​​as the example generates an error

The subquery returned more than one value, which is not correct when followed by =, !=, <, <=, >, >= or when used as an expression.

sql

1 Answers

  1. Best Answer

    The direct option is:

    CREATE TABLE ACL_FAC (ID_CARGA INT, SEQUENCIA INT)
GO
INSERT INTO ACL_FAC(ID_CARGA, SEQUENCIA)
VALUES
(4005946,680456522),
(4000000,680000000),
(4005947,680482070),
(4005948,680561046),
(4005949,680561049),
(4000001,680000001),
(4005950,680562329);
GO
-- Codigo Ejemplo 1

UPDATE ACL_FAC
  SET 
      ID_CARGA = SEQUENCIA
       WHERE ID_CARGA IN(4005946, 4005947, 4005948, 4005949, 4005950)
AND SEQUENCIA IN(680456522, 680482070, 680561046, 680561049, 680562329);

    Personally, I prefer to know what the result will be before doing the update and then apply it. For this I always use a common expression table. And with the output of this I cross it with the source table and see what I am going to do. Something like:

    ;WITH CTE
 AS (SELECT ID_CARGA
          , SEQUENCIA
     FROM 
          DBO.ACL_FAC
            WHERE ID_CARGA IN(4005946, 4005947, 4005948, 4005949, 4005950)
     AND SEQUENCIA IN(680456522, 680482070, 680561046, 680561049, 680562329))
 SELECT CASE
            WHEN CTE.ID_CARGA IS NULL
            THEN ACL_FAC.ID_CARGA
            ELSE CTE.SEQUENCIA
        END AS RESULTADO
 FROM 
      CTE
      RIGHT JOIN ACL_FAC ON CTE.ID_CARGA = ACL_FAC.ID_CARGA
                            AND CTE.SEQUENCIA = ACL_FAC.SEQUENCIA;
--)UPDATE CTE SET ID_CARGA = SEQUENCIA FROM CTE;

    Obtained result

    If the result obtained is as expected, then I comment the code of the select and I keep the update. If the set is complex, instead of directly updating the cte I perform an inner join against the table to be updated (in this case it is not necessary).

    WITH CTE
 AS (SELECT ID_CARGA
          , SEQUENCIA
     FROM 
          DBO.ACL_FAC
            WHERE ID_CARGA IN(4005946, 4005947, 4005948, 4005949, 4005950)
     AND SEQUENCIA IN(680456522, 680482070, 680561046, 680561049, 680562329))
 --SELECT CASE
 --           WHEN CTE.ID_CARGA IS NULL
 --           THEN ACL_FAC.ID_CARGA
 --           ELSE CTE.SEQUENCIA
 --       END AS RESULTADO
 --FROM 
 --     CTE
 --     RIGHT JOIN ACL_FAC ON CTE.ID_CARGA = ACL_FAC.ID_CARGA
 --                           AND CTE.SEQUENCIA = ACL_FAC.SEQUENCIA;
UPDATE CTE SET ID_CARGA = SEQUENCIA FROM CTE;
-- otra opción valida es: UPDATE A SET ID_CARGA = SEQUENCIA FROM ACL_FAC A INNER JOIN CTE ON CTE.ID_CARGA = ACL_FAC.ID_CARGA
--                           AND CTE.SEQUENCIA = ACL_FAC.SEQUENCIA;

    If you have never used common expression tables it may help you to read this: Cte

    • 1