Update a table with values from a different table

[sfmeier]

Hi,

in order to update a table with values from a different table I followed the description in...:

http://support.sas.com/documentation/cdl/en/sqlproc/62086/HTML/default/viewer.htm#a002595787.htm

This is what I wrote:

proc sql;

      delete from p_kontoAGG_rest2 as k

            where isin in (select isin from p_isin_mult)

                        and kusa in (select distinct kusa from p_isin_gruppe_join) and c200 not in (select c200 from output);

      update work.p_kontoAgg_rest2 as k

            set k.rwa = (select summe_rwa from p_isin_gruppen as i where k.isin=i.isin and k.Kusa = i.Kusa)

            where k.isin=i.isin and k.kusa = i.kusa;

quit;

But I got the following error... What did I do wrong?

14         proc sql;

15         /* Entfernung der Datensätze, deren RWA auf eine andere Position aufgeschlagen wird */

16         delete from p_kontoAGG_rest2 as k

17                where isin in (select isin from p_isin_mult)

18                           and kusa in (select distinct kusa from p_isin_gruppe_join) and c200 not in (select c200 from output);

NOTE: No rows were deleted from WORK.P_KONTOAGG_REST2.

19        

20         update p_kontoAgg_rest2 as k

21                set k.rwa = (select summe_rwa from p_isin_gruppen as i where k.isin=i.isin and k.Kusa = i.Kusa)

                  _

                  73

                  76

ERROR 73-322: Expecting an =.

ERROR 76-322: Syntax error, statement will be ignored.

Thx, S.

[Tom]

I think that it is actually complaining about the use of the alias prefix on the name of the variable to be updated.

Instead of K.RWA = ... just write RWA = ...  Here is an example that generates the same error message.

212  proc sql ;

213    update a

214      set a.age=(select age from b where a.name = b.name)

              -

              73

              76

ERROR 73-322: Expecting an =.

ERROR 76-322: Syntax error, statement will be ignored.

215    ;

216  quit;

The alias is not needed since you can only make changes to the variables in the table named in the UPDATE clause.

[Ksharp]

Maybe your error is from the UPDATE statement :

You need use sub-query to obtain the obs you need to be updated.

update work.p_kontoAgg_rest2 as k

            set k.rwa = (select summe_rwa from p_isin_gruppen as i where k.isin=i.isin and k.Kusa = i.Kusa)

where k.isin=(select isin from p_isin_gruppen as i where k.isin=i.isin and k.Kusa = i.Kusa) and k.kusa =(select kusa from p_isin_gruppen as i where k.isin=i.isin and k.Kusa = i.Kusa)

;

quit;

Ksharp