update error

[PBE3]

Hello all, I apologize if I am writing to the wrong forum, but if someone could help me out that'd be great. I have this update statement and it's not working.

update ACSPRDDTA.AAPCUST, ACSPRDDTA.AAPACCT set AAPCUST.AAFNAM='Mike', AAPCUST.AALNAM='cool', AAPCUST.AAADDR1='123 Applied Way',AAPCUST.AAADDR2='', AAPCUST.AACITY='Glen Mills', AAPCUST.AASTAT='PA', AAPCUST.AAZIP1='19342', AAPCUST.AAPHN1='1234567890', AAPCUST.AAEMAL='white@aol.com', AAPCUST.AAPSWD='aa', AAPACCT.ABCARD='4227096139001017', AAPACCT.ABEXPD='0109'where AAPACCT.AAMEMN=AAPCUST.AAMEMN

Any ideas?!? Thanks much.

 

[gmmastros]

You can only update data in one table with an update statement.

You should write multiple update statements to accomplish this.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[SQLDenis]

1) white@aol.com'...expect spam
2) an update is always one table at a time, you need two update statements
3) list the error next time, we are not mind readers and if we were we would be millionaires and not doing this stuff
4) use aliases
5) use a newer style ansi join
6) i hope that that is not a real address either


That where clause looks fishy also, your whole table will be update with the same data

untested

update c
set c.AAFNAM='Mike', 
c.AALNAM='cool', 
c.AAADDR1='123 Applied Way',
c.AAADDR2='', 
c.AACITY='Glen Mills', 
c.AASTAT='PA', 
c.AAZIP1='19342', 
c.AAPHN1='1234567890', 
c.AAEMAL='white@aol.com', 
c.AAPSWD='aa'
from ACSPRDDTA.AAPCUST c 
--fix this
JOIN ACSPRDDTA.AAPACCT t on t.AAMEMN=c.AAMEMN


update t
set t.ABCARD='4227096139001017', 
t.ABEXPD='0109'
from ACSPRDDTA.AAPCUST c 
--fix this
JOIN ACSPRDDTA.AAPACCT t on t.AAMEMN=c.AAMEMN

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
SQLBlog.com, Google Interview Questions