SQL Query help - Dev Shed

Dev Shed Forums Sponsor:

August 26th, 2005, 08:11 AM

dangerousjohnny

Contributing User

Join Date: Aug 2003

Posts: 109

Time spent in forums: 4 h 25 m 18 sec
Reputation Power: 6

SQL Query help

Hello,
Can someone pls help me w/ the following query? I'm trying to select all the records in the LIST table who have both a matching Postal Code + Address in the CLIENTS table.

UPDATE CLIENTS
SET DNS='YES'

WHERE PC IN (SELECT MAIL_CODE_UP FROM LIST WHERE PC=MAIL_CODE_UP)

AND

AD1 IN (SELECT ADDRESS1 FROM LIST WHERE ADDRESS1=AD1);

COMMIT;

August 26th, 2005, 08:23 AM

pabloj

Modding: Oracle MsSQL Firebird

Dev Shed God 6th Plane (7500 - 7999 posts)

Join Date: Jun 2001

Location: Outside US

Posts: 7,815

Time spent in forums: 2 Months 3 Weeks 1 Day 14 m 37 sec
Reputation Power: 278

But PC corresponds to MAIL_CODE or MAIL_CODE_UP in LIST table?

__________________
My blog about OpenSource Databases
PDF tutorials about OSS databases, DBMonster ...
Please contribute to Open Source Development, fill bug reports!!!
Developer Shed eSupport
Commented my.ini/my.cnf (PLEASE ADD YOUR OWN CONFIG TRICK)
An introduction to database normalization
Natural or Surrogate key
Custom ordering for your results
Correlated and uncorrelated subqueries
Don't turn your outer joins into inner joins

August 26th, 2005, 08:32 AM

dangerousjohnny

Contributing User

Join Date: Aug 2003

Posts: 109

Time spent in forums: 4 h 25 m 18 sec
Reputation Power: 6

Sorry, it should have read MAIL_CODE_UP, I fixed. But the problem is, it matches more than it should, for example in CLIENTS, PC 'M1N0P0' AD1 'PO BOX 21' will match LIST MAIL_CODE_UP'M1N0P0' ADDRESS1'PO BOX 789'

August 26th, 2005, 08:40 AM

pabloj

Modding: Oracle MsSQL Firebird

Join Date: Jun 2001

Location: Outside US

Posts: 7,815

Time spent in forums: 2 Months 3 Weeks 1 Day 14 m 37 sec
Reputation Power: 278

Shouldn't it be something like:

sql Code:

Original - sql Code
UPDATE CLIENTS SET DNS='YES' WHERE CLIENTS.PC=LIST.MAIL_CODE_UP AND CLIENTS.AD1 = LIST.ADDRESS1;

 
UPDATE CLIENTS
SET DNS='YES'
WHERE CLIENTS.PC=LIST.MAIL_CODE_UP
AND
CLIENTS.AD1 = LIST.ADDRESS1;

August 26th, 2005, 08:54 AM

dangerousjohnny

Contributing User

Join Date: Aug 2003

Posts: 109

Time spent in forums: 4 h 25 m 18 sec
Reputation Power: 6

I keep getting an invalid column reference error . Don't I have to select the LIST table first or something?

August 26th, 2005, 09:05 AM

pabloj

Modding: Oracle MsSQL Firebird

Join Date: Jun 2001

Location: Outside US

Posts: 7,815

Time spent in forums: 2 Months 3 Weeks 1 Day 14 m 37 sec
Reputation Power: 278

sql Code:

Original - sql Code
UPDATE CLIENTS SET DNS='YES' WHERE PC IN (SELECT MAIL_CODE_UP FROM LIST WHERE CLIENTS.PC=LIST.MAIL_CODE_UP AND LIST.ADDRESS1=CLIENTS.AD1) AND AD1 IN (SELECT ADDRESS1 FROM LIST WHERE CLIENTS.PC=LIST.MAIL_CODE_UP AND LIST.ADDRESS1=CLIENTS.AD1);

UPDATE CLIENTS
SET DNS='YES'
WHERE PC IN (SELECT MAIL_CODE_UP FROM LIST WHERE CLIENTS.PC=LIST.MAIL_CODE_UP AND LIST.ADDRESS1=CLIENTS.AD1)
AND
AD1 IN (SELECT ADDRESS1 FROM LIST WHERE CLIENTS.PC=LIST.MAIL_CODE_UP AND LIST.ADDRESS1=CLIENTS.AD1);

sql Code:

Original - sql Code
UPDATE CLIENTS, LIST SET DNS='YES' WHERE CLIENTS.PC=LIST.MAIL_CODE_UP AND CLIENTS.AD1 = LIST.ADDRESS1;

 
UPDATE CLIENTS, LIST
SET DNS='YES'
WHERE CLIENTS.PC=LIST.MAIL_CODE_UP
AND
CLIENTS.AD1 = LIST.ADDRESS1;

don't have Oracle at hand, sorry.

August 26th, 2005, 09:20 AM

dangerousjohnny

Contributing User

Join Date: Aug 2003

Posts: 109

Time spent in forums: 4 h 25 m 18 sec
Reputation Power: 6

thank you very much, that worked!

Thread Tools	Search this Thread
Email this Page	Search this Thread: Advanced Search
Display Modes	Rate This Thread
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode	Rate This Thread: