Update table

Last Post 02 Jan 2009 03:28 AM by JASBRYDEN. 2 Replies.
AddThis - Bookmarking and Sharing Button
Author Messages
JASBRYDEN
New Member
New Member

--
29 Dec 2008 06:07 AM
HI

I want to do an update on the table VCODETL.

However I only want it to update the coloum 'desc' when the following is meet.

rmcust like '0999%' and rmdel='A'

so it would look at the table VARCUST, if the above criticia is meet then update the coloum 'desc', with the word STAFF, if it does not them leave it alone.

This is what I have, but it is doing staff for all, which is not correct, it should only do 15 records.

PLEASE HELP !!!!


update VCODETL
set [desc]='staff'
from vcodetl
left join varcust on
oacmp=rmcmp and oacust=rmcust and
rmcust like '0999%' and rmdel='A'
JASBRYDEN
New Member
New Member

--
02 Jan 2009 03:28 AM
This is the VCODETL Table

OACMP OAORD OBLINE OACUST DESC OATYPE OALOC OAPRNT OAORDT OAINDT OAINV OARQDT OASMN1 OBQORD OBQSHP OBPRIC
1 475821 1 TEST NULL O 0101 I 20070629 20070629 754633 20070629 1016 1.00 1.00 91.5800
1 475821 2 TEST NULL O 0101 I 20070629 20070629 754633 20070629 1016 1.00 1.00 60.7800
1 445818 1 0000001 NULL O 0101 I 20070228 20070305 728259 20070228 1009 20.00 20.00 1230.0000
1 445818 2 0000001 NULL O 0101 I 20070228 20070305 728259 20070228 1009 10.00 10.00 1999.0000
1 445818 3 0000001 NULL O 0101 I 20070228 20070305 728259 20070228 1009 20.00 10.00 1235.0000


This is the VARCUST Table

RMCMP RMCUST RMDEL RMNAME RMADD1 RMADD2 RMSMNO RMLOC RMCCLS RMTERR
1 GAS01 D GAS MINI MART #108, CALCUTTA RD #2 FREEPORT 4087 402 23 CENT
1 1 A MISCELLANEOUS RECEIPTS 1099 101 21 1000
1 20 A BPTT LLC 5-5A QUEENS PARK PLAZA QUEENS PARK WEST 1009 101 21 1000
1 22 A DR MELVIN AKAL 118 SOUTHERN MAIN ROAD CUREPE 1009 101 17 2800
1 28 A ALPHA DRUGS 161 DIEGO MARTIN MAIN RD DIEGO MARTIN 1039 101 6 3100
SwePeso
New Member
New Member

--
05 Jan 2009 06:06 AM
UPDATE x
SET x.[dsc] = 'staff'
FROM VCODETL AS x
INNER JOIN (
SELECT DISTINCT rmcmp,
rmcust
FROM varcust
WHERE rmcust LIKE '0999%'
AND rmdel = 'A'
) AS w ON w.rmcmp = x.oacmp
AND w.rmcust = x.oacust


Acceptable Use Policy
---