I have added the following query but it doesn't work, after I added set x line. Please help me fix this query
BEGIN
DECLARE x INT;
SET x = SELECT donation_id FROM wp_give_donationmeta ORDER BY wp_give_donationmeta.meta_id DESC LIMIT 1;
UPDATE wp_posts SET post_status= 'Sponsored' WHERE ID= x;
END
Try to use EXISTS
BEGIN
UPDATE wp_posts
SET post_status= 'Sponsored'
WHERE EXISTS( SELECT donation_id
FROM wp_give_donationmeta
WHERE wp_posts.ID = wp_give_donationmeta .donation_id);
END
You could the update just using a single query
UPDATE wp_posts S
INNER JOIN (
SELECT donation_id
FROM wp_give_donationmeta
ORDER BY wp_give_donationmeta.meta_id DESC
LIMIT 1
) t
SET post_status= 'Sponsored' WHERE ID= t.donation_id;
assuming you are creating valid trigger then you could
BEGIN
UPDATE wp_posts S
INNER JOIN (
SELECT donation_id
FROM wp_give_donationmeta
ORDER BY wp_give_donationmeta.meta_id DESC
LIMIT 1
) t
SET post_status= 'Sponsored' WHERE ID= t.donation_id;
END;
