CODEWITHSUNDEEP
sqlsql-server
user10965585
user10965585

Reputation:

Tryng to update a table in SQL

I am trying to update my table using the query be low but it kept giving me errors. Please read the rem to see know the errors 

UPDATE VM1DTA.DCIRPG CM     -- invalid column name cm
SET ILCHDRNUM =             -- incorrect syntax near SET and =
    (SELECT ILCHDRNUM FROM VM1DTA.DCIARG WHERE INCHDRNUM = CM.CHDRNUM);
COMMIT;

UPDATE dbo.DCIRPG CM        -- invalid column name cm
SET ILPYCLT =               -- incorrect syntax near SET and =
    (SELECT CLNTKEY
     FROM dbo.DCIARG
     WHERE CLNTKEY = CM.PAYCLT --the multipath CM.PAYCLT can't bound
       AND SEQNUMB = 1
       AND);

Upvotes: 0

Views: 102

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1269503

SQL Server doesn't allow aliases in the UPDATE. You need to use a FROM clause:

UPDATE CM 
   SET ILCHDRNUM =    --incorrect syntax near SET and =
       (SELECT ILCHDRNUM FROM VM1DTA.DCIAPF WHERE INCHDRNUM = CM.CHDRNUM)
   FROM VM1DTA.DCIRGPF CM;

UPDATE CM     --invalid column name cm
    SET ILPYCLT =     --incorrect syntax near SET and =
           (SELECT CLNTKEY
            FROM dbo.DCIARG
            WHERE CLNTKEY = CM.PAYCLT AND
                  SEQNUMB = 1
           )
    FROM dbo.DCIRPG CM;

Upvotes: 1

Ketan Kotak
Ketan Kotak

Reputation: 957

You can try with following 

UPDATE CM  
SET CM.ILCHDRNUM =  DCI.ILCHDRNUM
from VM1DTA.DCIRPG CM
INNER JOIN VM1DTA.DCIARG DCI ON DCI.INCHDRNUM = CM.CHDRNUM

and second query 

UPDATE CM  
SET CM.ILPYCLT =    DCI.CLNTKEY        
FROM  dbo.DCIRPG CM 
INNER JOIN dbo.DCIARG DCI
ON  DCI.CLNTKEY = CM.PAYCLT 
AND DCI.SEQNUMB = 1

Upvotes: 0

Aaron Bertrand
Aaron Bertrand

Reputation: 280252

Alternatively:

UPDATE RPG
  SET RPG.ILCHDRNUM = ARG.ILCHDRNUM
  FROM VM1DTA.DCIRPG AS RPG 
  INNER JOIN VM1DTA.DCIARG AS ARG
  ON RPG.CHDRNUM = ARG.INCHDRNUM;

UPDATE RPG
  SET RPG.ILPYCLT = ARG.CLNTKEY
  FROM dbo.DCIRPG AS RPG
  INNER JOIN dbo.DCIARG AS ARG
  ON RPG.PAYCLT = ARG.CLNTKEY
  AND ARG.SEQNUMB = 1;

Also, whoever named these tables and columns needs a trip out behind the woodshed. Maybe several.

Upvotes: 1

Related Questions