Update query with select statement in MySQL

Instead of doing it using subquery, first store the PrdCde into a variable and use that for the update statement. Perhaps, this is not working because select statement is looking for the same table which is being updated.

Hope this will help.

Regards,
Sheo Narayan
http://www.dotnetfunda.com

Thiru, if this helps please login to Mark As Answer. | Alert Moderator

hi,

refer to this link for mysql update a table while selecting from it in a subquery.
http://www.xaprb.com/blog/2006/06/23/how-to-select-from-an-update-target-in-mysql/

Regards,
Bugwee

Thiru, if this helps please login to Mark As Answer. | Alert Moderator

Hi Bugwee,
Thanks for your reply and valuable link.

But still now i cant able to get a solution in this regard.

For your information:

I need to update a field multipled by 12
as: update tblbf set term=term*12 where prdcde = (products specified in a specified group)

This query will get me required prdcde:
select a.PrdCde from tblbf a, tblproduct b where (a.prdcde=b.prdcde) and (b.PrdGrp<>'SAPL')
(note this subquery will return multiple records)

How to UPDATE tblbf set term=term*12 where prdcde matching above subquery

Expecting your valuable support to get a solution.

Waiting for your reply.
Thanks & Regards,
Thiru.

Thiru, if this helps please login to Mark As Answer. | Alert Moderator

hi,

Are you using ASP.Net for this? or you just want to do it via database programming?

Regards,
Bugwee

Thiru, if this helps please login to Mark As Answer. | Alert Moderator

Just want to do with MySQL DataBase.

Thiru, if this helps please login to Mark As Answer. | Alert Moderator

hi Thiru,

have a try

DELIMITER $$



CREATE PROCEDURE CursorUpdateDemo()

BEGIN

  DECLARE done INT DEFAULT 0;

  DECLARE PrdCde CHAR(16);

  DECLARE cur1 CURSOR FOR select a.PrdCde from tblbf a, tblproduct b where (a.prdcde=b.prdcde) and (b.PrdGrp<>'SAPL');

  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;



  OPEN cur1;



  read_loop: LOOP

    FETCH cur1 INTO PrdCde;

    IF done THEN

      LEAVE read_loop;

    END IF;



    update tblbf set term=term*12

      where PrdCde = PrdCde;



  END LOOP;



  CLOSE cur1;



END $$



DELIMITER ;

Hope this help.

Regards,
Bugwee

Thiru, if this helps please login to Mark As Answer. | Alert Moderator

Thanks for spending your valuable time here.

Sorry to say -- i didn't use stored procedure -- can you tell me how & where to use this in MySQL using SQLyog or MySQL Query Browser

Thiru, if this helps please login to Mark As Answer. | Alert Moderator

Hi,

You can use both. But what i am using is MySQL Query Browser.
You can easily execute that stored procedure using

CALL CursorUpdateDemo()

in order to work

Regards,
Bugwee

Thiru, if this helps please login to Mark As Answer. | Alert Moderator

You mean:
Opening Script Tab and paste your code and execute?

-- Pls. let me know how to execute query browser here.

Thiru, if this helps please login to Mark As Answer. | Alert Moderator

Hi,

You can easily copy and paste the code (the stored procedure) on query browser then execute.
And to run or execute the update you wanted then using the query browser again

CALL CursorUpdateDemo()

Regards,
Bugwee

Thiru, if this helps please login to Mark As Answer. | Alert Moderator

Its getting following error:

Error Code : 1064
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'DELIMITER $$
CREATE PROCEDURE CursorUpdateDemo()
BEGIN
DECLARE done INT DEF' at line 1
(0 ms taken)

Error Code : 1064
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'DECLARE PrdCde CHAR(16)' at line 1
(0 ms taken)

Error Code : 1064
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'DECLARE cur1 CURSOR FOR select a.PrdCde from tblbf a, tblproduct b where (a.prdc' at line 1
(0 ms taken)

Error Code : 1064
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1' at line 1
(0 ms taken)

Error Code : 1064
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'OPEN cur1' at line 1
(0 ms taken)

Error Code : 1064
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'read_loop: LOOP
FETCH cur1 INTO PrdCde' at line 1
(16 ms taken)

Error Code : 1064
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'IF done THEN
LEAVE read_loop' at line 1
(0 ms taken)

Error Code : 1064
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'END IF' at line 1
(0 ms taken)

(13704 row(s) affected)
(359 ms taken)

Error Code : 1064
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'END LOOP' at line 1
(0 ms taken)

Error Code : 1064
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'CLOSE cur1' at line 1
(0 ms taken)

Error Code : 1064
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'END $$
DELIMITER' at line 1
(0 ms taken)

Thiru, if this helps please login to Mark As Answer. | Alert Moderator

Did you execute the stored procedure using the mysql query browser?
It doesn't return any error here in mine.

Regards,
Bugwee

Thiru, if this helps please login to Mark As Answer. | Alert Moderator

actually i am using the sample procedure here in my database as an example

DELIMITER $$



CREATE PROCEDURE Updatedemo()

BEGIN

  DECLARE done INT DEFAULT 0;

  DECLARE PrdCde CHAR(16);

  DECLARE cur1 CURSOR FOR SELECT abm FROM branch_info b where ABM=2;

  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;



  OPEN cur1;



  read_loop: LOOP

    FETCH cur1 INTO PrdCde;

    IF done THEN

      LEAVE read_loop;

    END IF;



    update branch_info set ABM=ABM*12

      where ABM = PrdCde;



  END LOOP;



  CLOSE cur1;



END $$



DELIMITER ;

Regards,
Bugwee

Thiru, if this helps please login to Mark As Answer. | Alert Moderator

In Query Browser:
Its showing:
Script line: 2 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'PROCEDURE CursorUpdateDemo()
BEGIN
DECLARE done INT DEFAULT 0;
DECLARE Pr' at line 1

staying with (progress:5.56%)

I just paste your code in NEW SCRIPT TAB

DELIMITER $$
CREATE PROCEDURE CursorUpdateDemo()
BEGIN
DECLARE done INT DEFAULT 0;
DECLARE PrdCde CHAR(16);
DECLARE cur1 CURSOR FOR select a.PrdCde from tblbf a, tblproduct b where (a.prdcde=b.prdcde) and (b.PrdGrp<>'SAPL');
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
OPEN cur1;
read_loop: LOOP
FETCH cur1 INTO PrdCde;
IF done THEN
LEAVE read_loop;
END IF;
update tblbf set term=term*12 where PrdCde = PrdCde;
END LOOP;
CLOSE cur1;
END $$
DELIMITER ;

Thiru, if this helps please login to Mark As Answer. | Alert Moderator

I think we have different mysql version. what i am using is '5.1.41'.
What about yours?

Regards,
Bugwee

Thiru, if this helps please login to Mark As Answer. | Alert Moderator

version of query browser is 1.2.17
version of mysql yog is 4.06
mysql-4.1.18-win32

Thiru, if this helps please login to Mark As Answer. | Alert Moderator