mySQL Procedure to update Table after insert (1 Viewer)

DKoehne

Registered User.
Local time
Yesterday, 19:06
Joined
Apr 10, 2017
Messages
49
Hello,



I have two tables, T1 and T2


T1 gets records created off the web via sql through php and that table triggers a copy to T2.



I then need T2 to send its PK back to T1 as an update to a field there to be used as a fk for future queries.



I am looking at setting up a procedure to do this in mysql (the backend) to call the sql within the procedure.



My objectives are twofold:

1) What is best practice for this type of scenario, and

2) code that works - what I have now isn't working (see below):


UPDATE usermanagement
SET usermanagement.tblClients_fldDRCClientID = ( SELECT
MAX(fldDRCClientID) AS MaxOffldDRCClientID
FROM
usermanagement, tblClients)
WHERE usermanagement(id) = MAX(id);


Note: they will both be the most recent records or last updated or max id's in the primary columns


Thanks!
 
Last edited:

Uncle Gizmo

Nifty Access Guy
Staff member
Local time
Today, 03:06
Joined
Jul 9, 2003
Messages
12,313
I note that your post has not received a reply, this may be because no one has an answer for you. Just in case it's been missed, I have bumped it up the list...
 

sonic8

AWF VIP
Local time
Today, 04:06
Joined
Oct 27, 2015
Messages
352
Despite reading this question and several similar ones a couple of times recently, the requirements pretty much remain a mystery to me.

I gather, you want to maintain some two-side cross-reference between the two tables. While this is a red flag on its own, your situation seems to be aggravated by the fact that there is no tangible definition of the relationship between those tables. - At least, I was unable to deduct it from your description of the problem.

I guess, your problem mostly results from a flawed table and relationship design.

Note: they will both be the most recent records or last updated or max id's in the primary columns
Is your application strictly single user and single session? - I very much doubt it. If it is not, your assumptions are wrong! You can never know if there wasn't another record inserted later than that one you are just processing.

As mentioned elsewhere already, use the LAST_INSERT_ID() function to retrieve the value of an AUTO_INCREMENT column after an insert.
 

Users who are viewing this thread

Top Bottom