Re: SQL. Correlated UPDATE problem.

From: Michel Cadot <micadot_at_netcourrier.com>
Date: Thu, 3 Feb 2000 10:55:24 +0100
Message-ID: <87bjah$168s$1@news6.isdnet.net>

v734> create table customers (cusno number primary key, cusname varchar2(50)); v734> create table customers_newinfo (cno number primary key, cname varchar2(50));

v734> insert into customers values (1,'Johnny');
v734> insert into customers values (2,'Amanda');
v734> insert into customers values (3,'Anthony');
v734> insert into customers values (4,'Elisabeth');
v734> insert into customers_newinfo values (2,'Amanda Smith');
v734> insert into customers_newinfo values (4,'Andy Welsh');
v734> commit;
v734> update (select a.cusname, b.cname
  2          from customers a, customers_newinfo b
  3          where a.cusno = b.cno)

  4 set cusname = cname ;

2 rows updated.

v734> select * from customers;

     CUSNO CUSNAME

---------- --------------------------------------------------
         1 Johnny
         2 Amanda Smith
         3 Anthony
         4 Andy Welsh

4 rows selected.

--
Have a nice day
Michel

Ivan Nilsson <dumb_at_nospam.com> a écrit dans le message : Olbm4.4510$jg4.9133_at_nntpserver.swip.net... This is a simplified version of my problem. I have these two tables

Customers
Cusno, cusname

1, Johhny
2, Amanda
3, Anthony
4, Elsabeth

Customers_newinfo
Cno, Cname
2, Amanda Smith
3, Andy Welsh

I wish to update Customers with the data from Customers_newinfo using one UPDATE statement.
Thus, customer 2 in Customer shall have the name changed to Amanda Smith, and Customer 3 to Andy Welsh.

My problem is to make the UPDATE-statement correlate the customer numbers from the two tables.

Anyone who knows how to do this?

/TIA Received on Thu Feb 03 2000 - 03:55:24 CST