Please help with simple PS/SQL-script

update TTT.CUSTOMER c
   set c.REMINDER_GROUP = 3
 where c.CUST_NO = TTT.NAME.ID_NO
   and c.REMINDER_GROUP <> 3
   and TTT.NAME.NAME_TYPE = 1

TTT.NAME is not referenced within the update = error

You will have to use either sub selects or inner joins or merges to solve this...

some examples:

MERGE:

MERGE INTO table_b 
USING 
(
  SELECT id,
         field_2
  FROM table_a
) ta ON (ta.id = table_b.id)
WHEN MATCHED THEN UPDATE 
    SET table_b.field_2 = ta.field_2

Select allOpen in new window

SUB SELECT:

UPDATE table_a a
   SET field_2 = ( SELECT field_2
                     FROM table_b b
                    WHERE b.id = a.id )
;

Select allOpen in new window

INNER JOIN:

UPDATE a
   SET a.field_2 = b.field_2
FROM table_a a
INNER JOIN table_b b
  ON a.id = b.id
;

Select allOpen in new window

Thanks for your answer, but I cannot see how any of those examples would work, as I want to set the value of REMINDER_GROUP to a fixed value of 3 for all records that match the WHERE statement. I don't want to set it to the result of a sub-query.

For each row in the TTT.CUSTOMER-table found by this query:

select c.rowid
  from TTT.NAME n, TTT.CUSTOMER c
 where n.ID_NO = c.CUST_NO
   and c.REMINDER_GROUP <> 3
   and n.NAME_TYPE = 1

Select allOpen in new window

I want to set REMINDER_GROUP to 3

IVer

Fixed it:

update CUSTOMER
   set REMINDER_GROUP = 3
 where CUST_NO in 
 (
       select cu.CUST_NO
       from NAME n
       inner join
       CUSTOMER cu
         on cu.CUST_NO = n.ID_NO
       where cu.REMINDER_GROUP <> 3
         and n.NAME_TYPE = 1
 )

Select allOpen in new window

did the trick.

IVer

I've requested that this question be closed as follows:

Accepted answer: 0 points for IverErling's comment #a39650898

for the following reason:

This script did exactly what I was looking for.

I am really not after the points, but I have the feeling that this kind of closing a question is not "ok"...

Thumbs up *rolling eyes*
Well, that went well...