Link to home
Start Free TrialLog in
Avatar of ale1981
ale1981

asked on

Loop select statement and update rows with the results?

I have a table which holds addresses for customers, what I want to do is update a table using a select statement. I have attached the seperate queries.

How can I loop through so all customers addresses with code 9998 are updated with the phone number from their address with code 0000?

Thanks in advance.
SELECT CUSTNMBR, ADRSCODE, PHONE1
FROM RM00102 
WHERE (ADRSCODE = '0000')
 
UPDATE RM00102 
SET PHONE1 = PHONE1 
WHERE (CUSTNMBR = CUSTNMBR) AND (ADRSCODE = '9998')

Open in new window

Avatar of Jinesh Kamdar
Jinesh Kamdar
Flag of India image


UPDATE RM00102 A
SET PHONE1 = (SELECT PHONE1 FROM RM00102 WHERE ADRSCODE = '0000'
AND CUSTNMBR = A.CUSTNMBR)
AND ADRSCODE = '9998';

Open in new window

ASKER CERTIFIED SOLUTION
Avatar of ee_rlee
ee_rlee
Flag of Philippines image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of ale1981
ale1981

ASKER

jinesh I get the following errors;

Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near 'A'.
Server: Msg 156, Level 15, State 1, Line 4
Incorrect syntax near the keyword 'AND'.

Can u try AS A ?
UPDATE RM00102 AS A
SET PHONE1 = (SELECT PHONE1 FROM RM00102 WHERE ADRSCODE = '0000' AND CUSTNMBR = A.CUSTNMBR)
AND ADRSCODE = '9998';

Open in new window

Avatar of ale1981

ASKER

Is there also a way I can limit the update statement to the firsrt 5 rows while testing as we have over 10,000 customers in the db?
Avatar of ale1981

ASKER

jinesh, still getting errors;

Server: Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'AS'.
Server: Msg 156, Level 15, State 1, Line 3
Incorrect syntax near the keyword 'AND'.
Avatar of ale1981

ASKER

By the way I am using SQL Server 2000
Oops, i goofed up! Try this.
UPDATE RM00102 A
SET A.PHONE1 = (SELECT PHONE1 FROM RM00102 WHERE ADRSCODE = '0000' AND CUSTNMBR = A.CUSTNMBR)
WHERE A.ADRSCODE = '9998';

Open in new window

it is possible but that will be a much more complicated query. can't you create a temp table just for testing?
Avatar of ale1981

ASKER

I created a temp table called tblRM00102 but am still getting errors using the code.


UPDATE tblRM00102 A
SET A.PHONE1 = (SELECT PHONE1 FROM tblRM00102 WHERE ADRSCODE = '0000' AND CUSTNMBR = A.CUSTNMBR)
WHERE A.ADRSCODE = '9998';
 
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near 'A'.
Server: Msg 156, Level 15, State 1, Line 3
Incorrect syntax near the keyword 'WHERE'.

Open in new window

Have you tried my code?
Avatar of ale1981

ASKER

Sorry ee_rlee, your code works perfect on my test table.
may i ask why a B?
Avatar of ale1981

ASKER

Sorry, is there a way i can change the grade, i was meant to give A.
it's all right. just thought that you are not satisfied. ;)
Avatar of ale1981

ASKER

No, it solved my problem and worked great :)