Reply
Sat 2 Apr, 2005 09:30 am
Hello everybody
I have a problem with this sql update statement and couldn't spot what's the problem is, can anyone give me an advice? Your advice is very appreciated
Here is the code:
update Admin SET Admin.username = AdminLogin.username from Admin, AdminLogin WHERE Admin.name = AdminLogin.name;
ERROR
update Admin SET Admin.username = AdminLogin.username
from Admin, AdminLogin WHERE Admin.name = AdminLogin.name ;
from Admin, AdminLogin WHERE Admin.name = AdminLogin.name
*
ERROR at line 2:
ORA-00933: SQL command not properly ended
I have an 'Admin' table and 'AdminLogin' table that look like this
Admin table fields AdminLogin field
Name Username Password Name UserName Password
John smith John Smith jsaa1 js001
in Admin table username and password is blank so I need to update the data from AdminLogin into Admin table. I am not allow to use insert into Admin values....... I have to use update.
you dont use the "from" keyword in an update statement. Try the following:
Code:
update Admin SET Admin.username = AdminLogin.username WHERE Admin.name = AdminLogin.name;
SQL> update Admin SET Admin.username = AdminLogin1.username WHERE Admin.name = AdminLogin.name;
WHERE Admin.name = AdminLogin.name
*
ERROR at line 2:
ORA-00904: "AdminLOGIN"."NAME": invalid identifier
do you think this is because of table creation has problem?
create table AdminLogin(
name varchar2(25),
username varchar2(20),
password varchar2(20),
primary key (name),
foreign key (name) references Admin);
create table Admin (
name varchar2(20),
username varchar2(20),
password varchar2(20),
address varchar2(40),
salary real,
phone varchar2(12),
primary key (name));
sorry the code i gave you forgot to say that you were updating 2 different tables, try:
Code:
update Admin, AdminLogin SET Admin.username = AdminLogin.username WHERE Admin.name = AdminLogin.name;
nighthawk wrote:sorry the code i gave you forgot to say that you were updating 2 different tables, try:
Code:
update Admin, AdminLogin SET Admin.username = AdminLogin.username WHERE Admin.name = AdminLogin.name;
thank you very much for your advice.
Cheer
Stumble It!
Tweet This
Bookmark on Delicious
Share on Facebook
Share on MySpace