problem in sql queries

Question

0.00/5 (No votes)

See more:

how to create procedure for following table ..
let I have the first table::::::

Name no
ABC 123
XYZ 365
XYZ 123

I just want to update that table as follows:

Name no

ABC 123
XYZ 365
ABC 123

Note:no 123 should have its first name at every entry of 123

Posted 9-Jul-12 17:04pm

project virus

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)

barneyman · Accepted Answer · 2012-07-09T18:00:00

Solution 1

You have a couple of problems to solve here, but I'll suggest some pointers

First, you have to get a list of all the numbers in the 'no' column, perhaps using a DISTINCT[^] query, perhaps into a temporary table[^]

Second, for each of the 'no' you get in the first query, you have to find all the 'Name' variants, perhaps walking through the temp table with a CURSOR[^] may be useful

Perhaps a select to get the first name that has the same 'no', using LIMIT[^] - only you know the definition of 'first', because that implies order, that we are unaware of

Then an UPDATE[^] of the original source table, using the 'name' you gleaned form the second query, where the 'no' is from the temp cursor row

All the clues are in there :)

Posted 9-Jul-12 18:00pm

barneyman

Updated 9-Jul-12 18:02pm

v2

Comments

project virus 10-Jul-12 1:12am

sounds good i will definitely try it..

project virus 10-Jul-12 1:22am

do i have to insert values in that temporary table?

barneyman 10-Jul-12 1:32am

try something like
CREATE TEMPORARY TABLE distinctNumbers AS (SELECT distinct [no] FROM yourTable)

project virus 10-Jul-12 1:25am

which cursor I have to use Fetch?close?open?declare??

barneyman 10-Jul-12 1:34am

you declare a cursor, then open it, then fetch from it in a loop, then close it

project virus 10-Jul-12 1:47am

:-)

project virus 10-Jul-12 1:56am

CREATE PROCEDURE curdemo()
BEGIN
DECLARE done INT DEFAULT FALSE;
DECLARE a CHAR(16);
DECLARE cur1 CURSOR FOR SELECT name FROM emp;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
OPEN cur1;
read_loop: LOOP
FETCH cur1 INTO a;
IF done THEN
LEAVE read_loop;
END IF;
END LOOP;
CLOSE cur1;
END;

is this write for CURSOR???

barneyman 10-Jul-12 1:59am

looks good - in fact, I was wrong, you don't need the temp table, you can just use that cursor using the distinct select ...

project virus 10-Jul-12 2:01am

then I have to declare here two cursors?? like cur1,cur2;

project virus 10-Jul-12 2:08am

How should I have to use LIMIT between the CURSOR

barneyman 10-Jul-12 2:14am

LOL - ok - short of writing it for you ... try this pseudocode


declare a cursor select distinct no from emp
for each @no=no
   select top 1 @mastername=name from emp where no=@no order by name
   update emp set name=@mastername where no=@no

i've used TOP 1 because i don't know the LIMIT syntax (I use Transact) and the order by qualifier is a guess on my part

project virus 10-Jul-12 2:27am

its not working...

barneyman 10-Jul-12 2:33am

which bit?

(you may need a temp table for the first distinct query, depends on how mysql handles the update locking on that cursor)

project virus 10-Jul-12 2:49am

i m getting error

#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 '[no] FROM sample)' at line 1 for following querry..
CREATE TEMPORARY TABLE dist AS (
SELECT distinct [no]
FROM sample
)

barneyman 10-Jul-12 2:54am

take the [ and ] out ... don't forget to drop the temp table on the way out

project virus 10-Jul-12 3:06am

I m getting error in this code while creating procedure..

CREATE PROCEDURE curdemo()
BEGIN
DECLARE done INT DEFAULT
FALSE;
DECLARE cur1 CURSOR FOR
SELECT name FROM sample;
DECLARE CONTINUE HANDLER FOR
NOT FOUND SET done=TRUE;
OPEN cur1;
read_loop:LOOP
FETCH cur1 into a;
IF done THEN
LEAVE read_loop;
END IF;
END LOOP;
CLOSE cur1;
END;

project virus 10-Jul-12 3:18am

which condition i have to write in the If...Else statement..?

project virus 10-Jul-12 3:21am

can i use php code for my problem??

project virus 10-Jul-12 3:29am

declare a cursor select distinct no from emp
for each @no=no
select top 1 @mastername=name from emp where no=@no order by name
update emp set name=@mastername where no=@no
what is this top1

project virus 10-Jul-12 3:45am

SORRY FOR YOUR INCONVENIENCE but please try to solve my proble:: what is wrong with the following.i m trying again and again but i can't..
CREATE PROCEDURE curdemo( ) BEGIN DECLARE CURSOR SELECT DISTINCT no
FROM emp
FOR each@no = nO
SELECT top1@name = name
FROM emp
WHERE no = @no
ORDER BY name
UPDATE emp SET name = @name WHERE no = @no CLOSE cur1;

barneyman 10-Jul-12 7:31am

ok - Top is not a MySql keyword - I explained that

I'll be honest now and explain my frustration ... I've now expended considerably more effort in helping you, than it would have taken for me to download MySql, install it, create your table, and write your SP

My intention was to point you in the right direction; my assumption was, that you learning by yourself how to do this would benefit you

You can try each line in isolation, before you put it into an SP; it's not 'all or nothing' - coding is about decomposition and iteration

I wish you well

project virus 10-Jul-12 8:44am

ok sir, thanks for the help. I will try it..

project virus 11-Jul-12 3:33am

I solved it