Update query with select statement in MySQL

Posted by Thiru under Others on 6/3/2011 | Points: 10 | Views : 8318 | Status : [Member] | Replies : 16
Expecting your valuable solution for this issue:

MySQL query is getting error while executing

Query: select term from tblbf where PrdCde = (select a.PrdCde from tblbf a, tblproduct b where (a.prdcde=b.prdcde) and (b.PrdGrp<>'SAPL'))
working fine and getting result

Query to execute:
update tblbf set term=term*12 where PrdCde = (select a.PrdCde from tblbf a, tblproduct b where (a.prdcde=b.prdcde) and (b.PrdGrp<>'SAPL'))
Not working, showing:
Error Code : 1093
You can't specify target table 'tblbf' for update in FROM clause

(0 ms taken)

Expecting your valuable reply in this regard.
Regards,
Thiru.




Responses

Posted by: SheoNarayan on: 6/3/2011 [Administrator] HonoraryPlatinum | Points: 25

Up
0
Down
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

Posted by: Bugwee on: 6/3/2011 [Member] Starter | Points: 25

Up
0
Down
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

Posted by: Thiru on: 6/4/2011 [Member] Starter | Points: 25

Up
0
Down
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

Posted by: Bugwee on: 6/4/2011 [Member] Starter | Points: 25

Up
0
Down
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

Posted by: Thiru on: 6/4/2011 [Member] Starter | Points: 25

Up
0
Down
Just want to do with MySQL DataBase.

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

Posted by: Bugwee on: 6/4/2011 [Member] Starter | Points: 25

Up
0
Down
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

Posted by: Thiru on: 6/4/2011 [Member] Starter | Points: 25

Up
0
Down
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

Posted by: Bugwee on: 6/4/2011 [Member] Starter | Points: 25

Up
0
Down
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

Posted by: Thiru on: 6/4/2011 [Member] Starter | Points: 25

Up
0
Down
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

Posted by: Bugwee on: 6/4/2011 [Member] Starter | Points: 25

Up
0
Down
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

Posted by: Thiru on: 6/4/2011 [Member] Starter | Points: 25

Up
0
Down
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

Posted by: Bugwee on: 6/4/2011 [Member] Starter | Points: 25

Up
0
Down
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

Posted by: Bugwee on: 6/4/2011 [Member] Starter | Points: 25

Up
0
Down
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

Posted by: Thiru on: 6/4/2011 [Member] Starter | Points: 25

Up
0
Down
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

Posted by: Bugwee on: 6/4/2011 [Member] Starter | Points: 25

Up
0
Down
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

Posted by: Thiru on: 6/4/2011 [Member] Starter | Points: 25

Up
0
Down
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

Login to post response