daleimi
asked on
SQL Update Statement Using 2 Tables
I am attempting to update two tables. I have a parent table and child table, and when updating the parent table I also want information in the child table to be updated as well, but I cannot seem to get the update statement to work as expected.
What I am attempting here is when updating the parent table to also update the ttblSalesAppt table column txtAppts.
I already have a select statement where the information is pulling correctly from the ttblSalesAppt table, but at this point I am having to update that table seperatly in order for the select statement to show up correctly.
I hope I have provided enough information regarding this issue.
Would it be smarter to attempt a stored procedure for this also, I have never written one, but have read where this could be used for this situation.
What I am attempting here is when updating the parent table to also update the ttblSalesAppt table column txtAppts.
I already have a select statement where the information is pulling correctly from the ttblSalesAppt table, but at this point I am having to update that table seperatly in order for the select statement to show up correctly.
I hope I have provided enough information regarding this issue.
Would it be smarter to attempt a stored procedure for this also, I have never written one, but have read where this could be used for this situation.
UPDATE tblSalesManifestGAA
SET txtLocation = @txtLocation, dtDate = @dtDate, dtPartyTime = @dtPartyTime, txtLNameGuest = @txtLNameGuest, txtFNameGuest = @txtFNameGuest,
txtProgCode = @txtProgCode, txtMktgComment = @txtMktgComment, txtBooker = @txtBooker, txtComments = @txtComments, ckShow = @ckShow,
ckNoShow = @ckNoShow
FROM tblSalesManifestGAA INNER JOIN
ttblSalesAppt ON tblSalesManifestGAA.pkSalesManGAAID = ttblSalesAppt.pkSalesManGAAID
WHERE (tblSalesManifestGAA.pkSalesManGAAID = @pkSalesManGAAID)
ViewUpdate.bmp
You cannot update two tables with one statement. You would have to use two separate statements.
ASKER
Okay so how would this be accomplished using just a Cross Join, and updating each table as a seperate entity seperated by a " ; " or just two statements seperate within the builder such as:
Update tableA
Set txtsomething=@txtsomething
From tableA
Where txtsomething=@txtsomething
Update tableB
Set txtsomethingb=@txtsomethin gb
From tableB
Where txtsomethingb=@txtsomethin gb
Am I on the right track here, or completely off?
Update tableA
Set txtsomething=@txtsomething
From tableA
Where txtsomething=@txtsomething
Update tableB
Set txtsomethingb=@txtsomethin
From tableB
Where txtsomethingb=@txtsomethin
Am I on the right track here, or completely off?
That's not a cross join. A cross join is a join whereby every record from one table is joined with every record from another. Thus producing a cartesian.
Yes, two update statements.
Yes, two update statements.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Instead of writing any code to update the column in other table, if you want to store same value in column of other table, then it would be recommended to use relationship between two table.
What you can do is make a relationship between two tables and set cascade update. The child table will be automaitically updated when the value in parent table changes.
What you can do is make a relationship between two tables and set cascade update. The child table will be automaitically updated when the value in parent table changes.
ASKER
Yeah that is a new concept for me, have not been working with SQL for very long, I am still trying to figure out the relationship of Primary key to Foreign key.
Are there any sites you can direct me to in order to accomplish this task.
Any help would be appreciated.
Are there any sites you can direct me to in order to accomplish this task.
Any help would be appreciated.