[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 271
  • Last Modified:

Please help with simple PS/SQL-script

Hi!

This works:
Select n.ID_NO,c.REMINDER_GROUP,c.CUST_NO,n.NAME_TYPE
  from TTT.NAME n, TTT.CUSTOMER c
 where c.CUST_NO = n.ID_NO
   and c.REMINDER_GROUP <> 3
   and n.NAME_TYPE = 1

Open in new window

Can anyone explain why this doesn't work:
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

Open in new window

And neither does this:
update cust set cust.REMINDER_GROUP = 3
    on TTT.CUSTOMER cust inner join TTT.NAME n 
 where cust.CUST_NO = n.ID_NO
   and cust.REMINDER_GROUP <> 3
   and n.NAME_TYPE = 1
 

Open in new window

Thanks for any help!
0
Iver Erling Arva
Asked:
Iver Erling Arva
  • 3
  • 3
1 Solution
 
Alexander Eßer [Alex140181]Software DeveloperCommented:
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

Open in new window


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

Open 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
;

Open in new window

0
 
Iver Erling ArvaSenior consultantAuthor Commented:
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

Open in new window

I want to set REMINDER_GROUP to 3

IVer
0
 
Iver Erling ArvaSenior consultantAuthor Commented:
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
 )

Open in new window

did the trick.

IVer
0
Veeam and MySQL: How to Perform Backup & Recovery

MySQL and the MariaDB variant are among the most used databases in Linux environments, and many critical applications support their data on them. Watch this recorded webinar to find out how Veeam Backup & Replication allows you to get consistent backups of MySQL databases.

 
Iver Erling ArvaSenior consultantAuthor Commented:
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.
0
 
Alexander Eßer [Alex140181]Software DeveloperCommented:
I am really not after the points, but I have the feeling that this kind of closing a question is not "ok"...
0
 
Mark GeerlingsDatabase AdministratorCommented:
Or, this would work with a sub-query in Oracle's native (not ANSI-standard) syntax:

update TTT.CUSTOMER c
   set c.REMINDER_GROUP = 3
 where c.REMINDER_GROUP <> 3
   and c.CUST_NO in (select N.ID_NO
   from TTT.NAME N
   where N.NAME_TYPE = 1);

Also: note that this is simply an SQL syntax question.  This is not a PL\SQL question.  PL\SQL is Oracle's programming language for stored procedures.  But, your question and the proposed solutions here do not involve stored procedures.
0
 
Alexander Eßer [Alex140181]Software DeveloperCommented:
Thumbs up *rolling eyes*
Well, that went well...
0

Featured Post

Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now