Solved

Please help with simple PS/SQL-script

Posted on 2013-11-15
7
257 Views
Last Modified: 2013-12-12
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
Comment
Question by:IverErling
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
7 Comments
 
LVL 13

Expert Comment

by:Alexander Eßer [Alex140181]
ID: 39650798
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
 

Author Comment

by:IverErling
ID: 39650836
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
 

Author Comment

by:IverErling
ID: 39650898
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
MS Dynamics Made Instantly Simpler

Make Your Microsoft Dynamics Investment Count  & Drastically Decrease Training Time by Providing Intuitive Step-By-Step WalkThru Tutorials.

 

Author Comment

by:IverErling
ID: 39650985
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
 
LVL 13

Expert Comment

by:Alexander Eßer [Alex140181]
ID: 39650986
I am really not after the points, but I have the feeling that this kind of closing a question is not "ok"...
0
 
LVL 35

Accepted Solution

by:
Mark Geerlings earned 500 total points
ID: 39651952
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
 
LVL 13

Expert Comment

by:Alexander Eßer [Alex140181]
ID: 39713936
Thumbs up *rolling eyes*
Well, that went well...
0

Featured Post

Revamp Your Training Process

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

690 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question