Can't get update to work!! [message #373164] |
Fri, 30 March 2001 11:39 |
Abbie
Messages: 1 Registered: March 2001
|
Junior Member |
|
|
I have 2 tables that contain a system-generated part_no that system creates whenever a user enters a part number. Table P also has a user_part_no field that contains the actual part number the user has entered and this field corresponds to the system-generated part_no field (but the numbers are different). Table P and Table PSP can be joined on part_no. I need to update the person field in Table PSP but I only want to update those records that contain a specific person_no and also have user_part_no (from table P) that begin with certain characters.
Here is what I have tried:
UPDATE part_seg_purch
SET pers_no='4509'
WHERE user_part_no IN (SELECT user_part_no FROM part WHERE user_part_no LIKE '05%');
and I've tried
UDPATE part_seg_purch psp
INNER JOIN part p on psp.part_no = p.part_no
SET pers_no='4509'
WHERE user_part_no IN (SELECT user_part_no from p WHERE user_part_no LIKE '05%')
AND pers_no='1479';
Here is a sample from a Select statement that joins the 2 tables and selects USERPN using a LIKE statment
USERPN BUYER SYSTEMPN
============================== ========== ==========
058577-030 1479 50511
058577-016 1479 50513
058577-020 1479 50512
058577-230 1479 50514
058577-220 1479 50515
Thanks for your help!!!
|
|
|
Re: Can't get update to work!! [message #373179 is a reply to message #373164] |
Mon, 02 April 2001 11:28 |
Cindy
Messages: 88 Registered: November 1999
|
Member |
|
|
If I understand your question correctly. Try this:
UPDATE part_seg_purch
SET pers_no=4509
WHERE part_seg_purch.part_no = (SELECT part_no
FROM table_p
WHERE user_part_no LIKE '05%')
AND pers_no=1479;
Note: I am assuming the pers_no is a NUMBER and the pers_no field is one of the fields in part_seg_purch table.
Hope this help.
-- Cindy
|
|
|