mySQL Procedure to update another Table after insert (1 Viewer)

DKoehne

Registered User.
Local time
Yesterday, 18:00
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!
--------------------------------------------------------------------------


Below is the procedure which is throwing an "Invalid use of group function"


DELIMITER //

DROP PROCEDURE IF EXISTS `clientsfk_ToUsermanagement`//

CREATE PROCEDURE `clientsfk_ToUsermanagement`()
BEGIN
UPDATE usermanagement
SET usermanagement.tblClients_fldDRCClientID = (SELECT MAX(tblClients.fldDRCClientID)
FROM tblClients
WHERE tblClients.fldDRCClientID = tblClients.fldDRCClientID)
WHERE usermanagement.id = MAX(id);

END//

DELIMITER ;
 
Last edited:

Insane_ai

Not Really an A.I.
Local time
Yesterday, 21:00
Joined
Mar 20, 2009
Messages
264
You posted the same question here at the end of business (US Eastern) yesterday here https://www.access-programmers.co.uk/forums/showthread.php?t=300839
Then posted this at 10 AM (US Eastern) which is basically the beginning of the day.


You may consider giving your question some time to get a response in the future rather than double posting it in different areas of the forum.

One problem is in the subquery, it needs to be identified and joined:
Code:
( SELECT
MAX(fldDRCClientID) AS MaxOffldDRCClientID
FROM
usermanagement, tblClients)


Treat the subquery as a joined table in the SQL and think about how you will join this to the other data. You will need to adjust this a bit to fit your environment but it should get you close

Code:
UPDATE usermanagement SET usermanagement.tblClients_fldDRCClientID = 
( 
SELECT MAX(fldDRCClientID) AS MaxOffldDRCClientID, [B][Primary Key Column]
[/B]FROM usermanagement
) as UM
INNER JOIN UM ON tblClients.[B][Column Name][/B]=usermanagement[B].[Primary Key Column][/B])
WHERE usermanagement(id) = MAX(id);



You could also try this:
Code:
UPDATE usermanagement 
SET usermanagement.tblClients_fldDRCClientID = (DMax("fldDRCClientID","usermanagement"))
WHERE usermanagement(id) = MAX(id);


I expect there will be adjustments to make because I can't see your environment to be sure. Also, it is not clear to me whether you intended to choose MaxOffldDRCClientID from the usermanagement table or the tblClients table, I presumed the first for my response.
 

DKoehne

Registered User.
Local time
Yesterday, 18:00
Joined
Apr 10, 2017
Messages
49
I was choosing MaxOffldDRCClientID from the tblClients (since that is the primary key and max is the most recent record created.) I then want to update or set that result into the 'usermanagement' table in the foreign key field. Right now, it is not working with any options attempted. The where clause is the challenge it seems.
 

Mark_

Longboard on the internet
Local time
Yesterday, 18:00
Joined
Sep 12, 2017
Messages
2,111
What is the unique identifier in the table you want to update? If that same unique identifier is present in the table you are reading from, use it to select the matching record (Where UniqueID = UniqueIDValue).

If there is NO Unique identifier, how will you know you are getting the correct record?
 

DKoehne

Registered User.
Local time
Yesterday, 18:00
Joined
Apr 10, 2017
Messages
49
It's the primary key named "id" is the max(id). But I run into a problem with max in the where clause and it chokes. Here is what I have it works fine up to the where clause:


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

Mark_

Longboard on the internet
Local time
Yesterday, 18:00
Joined
Sep 12, 2017
Messages
2,111
I don't think you can use MAX() like that. MIN() and MAX() are used with a SELECT.

I think you would need to use SELECT MAX(ID) FROM TblTable to find your ID first, then pass it to your UPDATE as a value.
 

Users who are viewing this thread

Top Bottom