CODEWITHSUNDEEP
sqlsql-server
dubfish
dubfish

Reputation: 29

Updating one table based on matching values of another

edit: this is SQL Server, not MYSQL as I mistagged it to be

I have the statement below that I am trying to update the F4105 table, designating it as 'a' setting the COUNCS column to a blank where the COLEDG column is equal to 07.. but, in doing this the statement needs to match up it's COLITM column with the IMLITM column of F4101 and make sure that the IMGLPT colum of F4101 does not contain values starting with FG, IN, or RM.

update CRPDTA.f4105bak a
set a.COUNCS=0
where a.COLEDG='07'
and exists (
select b.IMLITM from CRPDTA.f4101bak b
where a.COLITM=b.IMLITM
and substring(b.IMGLPT,1,2) not in ('FG','IN','RM'));

I can run this statement as a select * from after removing the second line, but when I try to run the statement as is above it throws the following exception error on the 'a' in the first line:

Msg 102, Level 15, State 1, Line 1
Incorrect syntax near 'a'.

Thanks!

Upvotes: 1

Views: 67

Answers (1)

Denis
Denis

Reputation: 12077

You can't rename after the update keyword, only in the "FROM" clause:

Something like this:

update a
set COUNCS=0
FROM CRPDTA.f4105bak a
where a.COLEDG='07'
and exists (
select b.IMLITM from CRPDTA.f4101bak b
where a.COLITM=b.IMLITM
and substring(b.IMGLPT,1,2) not in ('FG','IN','RM'));

but would be better as:

update a
set COUNCS=0
FROM CRPDTA.f4105bak a
     INNER JOIN CRPDTA.f4101bak b ON
        a.COLITM=b.IMLITM
WHERE a.COLEDG='07'
and substring(b.IMGLPT,1,2) not in ('FG','IN','RM'));

Upvotes: 1

Related Questions