How can I replace existing data in mysql?

Question

0.00/5 (No votes)

See more:

SQL

PROCEDURE `MY_Products_InsertUpdateByDelivery`(IN _myId INT, _myProductName VARCHAR(50), _myProductSection INT, _myProductReleaseStatus VARCHAR(50), _myProductDeliveryStatus VARCHAR(50))

BEGIN
      IF EXISTS(SELECT Product_Section from tbl_products Where Product_Name LIKE _myProductName AND 
        Product_Release_Status LIKE 'Partial') THEN
      UPDATE 
            tbl_products
      SET
            Product_Release_Status = _myProductReleaseStatus,
            Product_Delivery_Status = _myProductDeliveryStatus
      WHERE
            Product_Section = _myProductSection
      AND
            Product_Name = _myProductName;

-- Once i updated the existing partial.
-- I want to delete the origin of update to avoid duplication of row by deleting it.

    DELETE FROM
           tbl_products
    WHERE
        Id = _myId;

ELSE
      UPDATE 
            tbl_products
      SET
            Product_Release_Status = _myProductReleaseStatus,
            Product_Delivery_Status = _myProductDeliveryStatus
      WHERE
            Product_Section = _myProductSection
      AND
            Product_Name = _myProductName;
    END IF;
END

Posted 17-Nov-21 3:35am

Ice Bear

Updated 30-Nov-21 5:16am

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CHill60 · Answer 1 · 2021-11-30T05:16:00

When I run your code I do not get the results you claim - I get (I've shortened the column names)

Id	Productname	ProductSection	Release_Status	Delivery_Status
1	Paper		101				Full			Finished
2	Paper		101				Full			Finished

Based on your description of what you are trying to do, I would first identify the Id of the "origin of update" - which seems to be this line

| 1 | Paper | 101 | Full | Ongoing |

E.g. something like this (ssql not mysql but demonstrates what I mean)

SQL

DECLARE @IDFOUND INT = (SELECT ID FROM tbl_products WHERE Product_Name LIKE @_myProductName AND 
        Product_Delivery_Status LIKE 'Ongoing')

Then you can do your update finally the delete uses the "found" id

SQL

DELETE FROM
           tbl_products
    WHERE
        Id = @IDFOUND;

To pass the Id into the SP you must first know what has prompted the update, but you can do that processing within the SP