Updating multiple tables with one query????Possible?? 1

[KOVMoe]

I would like to update several tables, in one database, with one statement. My user will update up to 10 fields, then submit. I was wondering, is this on the right track?

UPDATE TTIXCUS_CUSNBR, TTIXCTC_CUSCTC
SET TTIXCUS_CUSNBR.SHN = '1234567', TTIXCTC_CUSCTC.CUS_CTC_PHN_NR = '1236549871'
WHERE TTIXCUS_CUSNBR.PFL_ID_NR =
(select F.PFL_ID_NR

from TTIXCAD_CUSADD A
INNER JOIN TTIXCUS_CUSNBR F ON A.CUS_ID_NR = F.CUS_ID_NR
INNER JOIN TTIXCTY_CITYNAME B ON A.CTY_ID_NR = B.CTY_ID_NR
INNER JOIN TTIXSTA_STATENAME C ON A.ST_ID_NR = C.ST_ID_NR
INNER JOIN TTIXCTC_CUSCTC D ON A.CUS_ID_NR = D.CUS_ID_NR
INNER JOIN TTIXSLT_SALUTATION E ON D.SLT_ID_NR = E.SLT_ID_NR
where F.pfl_id_nr = '1'
and F.shn_nr = '123456'
and A.cus_ad_te = '123'
and D.CUS_CTC_PHN_NR = '9725551212')

Thanks,
KOVMoe, Moe-King of the village idiots.
"When in trouble,
When in doubt-
Run in circles,
SCREAM & SHOUT!"
Burma Shave

 

[SQLSister]

NO, you cannot and should not ever consider updating multiple tables in one statement. Write a stored procedure that does one update statement for each table and includes transaction processing so that if one update fails all are rolled back. You may need to do the updates in a specific order, too, depending on the relationships, constraints and triggers.

Questions about posting. See faq183-874