Home » SQL & PL/SQL » SQL & PL/SQL » SQL query for update
SQL query for update [message #304853] Thu, 06 March 2008 23:49 Go to next message
jeer
Messages: 37
Registered: February 2008
Location: pakistan
Member
hi

I got two tables
BOOK (DDC,TITLE,ISBN,AUTH_NAME)
AUTHOR (DDC,AUTH_NAME)

Table "BOOK" contains about 50000 records but its
AUTH_NAME column contains NULL for all 50000 records.
I want insert AUTH_NAME column's values from AUTHOR table
into BOOK's AUTH_NAME column where DDC number match.

Please write me the query for it.

thanks

[LF] Several topics have been merged during last few days.

@Jeer, please, stop opening a new topic for every new idea - continue discussion in the original topic you have opened in order to solve this problem.

[Updated on: Mon, 10 March 2008 05:54] by Moderator

Report message to a moderator

Re: SQL Query for insertion [message #304854 is a reply to message #304853] Thu, 06 March 2008 23:52 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Use UPDATE.
Post what you already tried.

Regards
Michel
Re: SQL Query for insertion [message #304856 is a reply to message #304853] Thu, 06 March 2008 23:53 Go to previous messageGo to next message
BlackSwan
Messages: 25033
Registered: January 2009
Location: SoCal
Senior Member
http://www.orafaq.com/forum/t/88153/0/
How well does your post conform to posting guidelines as stated in URL above?

Why should we do your homework assignment for you?
Re: SQL Query for insertion [message #304859 is a reply to message #304854] Thu, 06 March 2008 23:58 Go to previous messageGo to next message
jeer
Messages: 37
Registered: February 2008
Location: pakistan
Member
I used this query before but result is confusing.

UPDATE BOOK SET BOOK.AUTH_NAME = AUTHOR.AUTH_NAME where BOOK.DDC = AUTHOR.DDC;
Re: SQL Query for insertion [message #304862 is a reply to message #304859] Fri, 07 March 2008 00:06 Go to previous messageGo to next message
Littlefoot
Messages: 20888
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Confusing? What a modest word!

Did you ever take time to read documentation about use of the UPDATE statement?
Re: SQL Query for insertion [message #304864 is a reply to message #304862] Fri, 07 March 2008 00:09 Go to previous messageGo to next message
jeer
Messages: 37
Registered: February 2008
Location: pakistan
Member
I am at very beginner level
Re: SQL Query for insertion [message #304869 is a reply to message #304864] Fri, 07 March 2008 00:16 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
UPDATE

Regards
Michel
Re: SQL Query for insertion [message #304871 is a reply to message #304864] Fri, 07 March 2008 00:16 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
So, explain what's so confusing. Do you get an error message? Do all columns get a little green alien as value?
Re: SQL Query for insertion [message #304872 is a reply to message #304853] Fri, 07 March 2008 00:17 Go to previous messageGo to next message
mshrkshl
Messages: 247
Registered: September 2006
Location: New Delhi
Senior Member
Quote:
I used this query before but result is confusing.



how?

regards,

[Updated on: Fri, 07 March 2008 00:17]

Report message to a moderator

Re: SQL Query for insertion [message #304874 is a reply to message #304871] Fri, 07 March 2008 00:31 Go to previous messageGo to next message
jeer
Messages: 37
Registered: February 2008
Location: pakistan
Member
DDC numbers do not match
Re: SQL Query for insertion [message #304876 is a reply to message #304853] Fri, 07 March 2008 00:52 Go to previous messageGo to next message
mshrkshl
Messages: 247
Registered: September 2006
Location: New Delhi
Senior Member
  1* select * from book
SQL> /

       DDC AUTHOR
---------- --------------------
         1


SQL> select * from author;

       DDC NAME
---------- --------------------
         1 rita

SQL> ed
Wrote file afiedt.buf

  1* update book set book.author=author.name where book.ddc=author.ddc
SQL> /
update book set book.author=author.name where book.ddc=author.ddc
                                                       *
ERROR at line 1:
ORA-00904: "AUTHOR"."DDC": invalid identifier


SQL> ed
Wrote file afiedt.buf

  1* update book set author=(select name from author where book.ddc=author.ddc)
SQL> /

1 row updated.

SQL> select * from  book;

       DDC AUTHOR
---------- --------------------
         1 rita



regards,
Re: SQL Query for insertion [message #304877 is a reply to message #304871] Fri, 07 March 2008 00:54 Go to previous messageGo to next message
kecd_deepak
Messages: 52
Registered: December 2007
Member
Hi,
How your querry work.....
You use auther table in querry but from where it select data of auther table.

Use UPDATE with Subquerry to update auther_name.

Regards
Deepak
Re: SQL Query for insertion [message #304886 is a reply to message #304876] Fri, 07 March 2008 01:11 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
@mshrkshl

Once again your solution is WRONG.
It works if you only have 1 row.

Please STOP spoonfeeding, or rather trying to.
Please STOP posting wrong answers. Verify them BEFORE.

Regards
Michel
Re: SQL Query for insertion [message #304899 is a reply to message #304853] Fri, 07 March 2008 01:56 Go to previous messageGo to next message
mshrkshl
Messages: 247
Registered: September 2006
Location: New Delhi
Senior Member
Quote:
@mshrkshl

Once again your solution is WRONG.
It works if you only have 1 row.



what is wrong?

SQL> select * from book;

       DDC AUTHOR
---------- --------------------
         1
         2
         3
         4
         2
         4

6 rows selected.

SQL> select * from author;

       DDC NAME
---------- --------------------
         1 ghf
         2 hjdfs
         3 tygy
         4 mkhy

SQL> update book set author=(select name from author where book.ddc=author.ddc);

6 rows updated.

SQL> select * from book;

       DDC AUTHOR
---------- --------------------
         1 ghf
         2 hjdfs
         3 tygy
         4 mkhy
         2 hjdfs
         4 mkhy

6 rows selected.


regards,
Re: SQL Query for insertion [message #304900 is a reply to message #304899] Fri, 07 March 2008 02:08 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
mshrkshl wrote on Fri, 07 March 2008 08:56
what is wrong?


insert into author (ddc, name) values (1, 'Oops');



[Edit: typo]

[Updated on: Fri, 07 March 2008 02:08]

Report message to a moderator

Re: SQL Query for insertion [message #304907 is a reply to message #304853] Fri, 07 March 2008 02:31 Go to previous messageGo to next message
mshrkshl
Messages: 247
Registered: September 2006
Location: New Delhi
Senior Member
if we update book table from selecting name from author table as matching ddc then ddc must be unique in author table,otherwise it will be an another case.

regards,
Re: SQL Query for insertion [message #304912 is a reply to message #304907] Fri, 07 March 2008 02:41 Go to previous messageGo to next message
pablolee
Messages: 2834
Registered: May 2007
Location: Scotland
Senior Member
Really good article on correlated subqueries from Ross Leishman's blog.
Re: SQL Query for insertion [message #305170 is a reply to message #304912] Sun, 09 March 2008 23:00 Go to previous messageGo to next message
jeer
Messages: 37
Registered: February 2008
Location: pakistan
Member
DDC Number is not unique. I want to update the table BOOK's column when it get the first matching value. After first matching value, rest should be ignored. Query should store the first matching AUTHOR_NAME in table BOOK.
SQL Query for update [message #305173 is a reply to message #304853] Sun, 09 March 2008 23:43 Go to previous messageGo to next message
jeer
Messages: 37
Registered: February 2008
Location: pakistan
Member
hi experts

I got two tables
BOOK (DDC,TITLE,ISBN,AUTH_NAME)
AUTHOR (ID,DDC,AUTH_NAME)

AUTH_NAME column in BOOK's table contains NULL for all records.

I want update table BOOK's column AUTH_NAME from AUTH_NAME column in AUTHOR table where DDC numbers match. DDC is not unique in AUTHOR's table. I want to update BOOK table with the first matching AUTH_NAME. Rest should be ignored.

Please write me the query for it.

thanks
Re: SQL Query for update [message #305177 is a reply to message #305173] Mon, 10 March 2008 00:12 Go to previous messageGo to next message
dhananjay
Messages: 635
Registered: March 2002
Location: Mumbai
Senior Member
And how about posting what you did.What was the problem you encountered while doing so?


regards,
Re: SQL Query for update [message #305184 is a reply to message #305173] Mon, 10 March 2008 01:21 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Don't repost the same question, continue on the previous topic.

Regards
Michel
updating table [message #305289 is a reply to message #304853] Mon, 10 March 2008 05:42 Go to previous messageGo to next message
jeer
Messages: 37
Registered: February 2008
Location: pakistan
Member
hi experts

I got two tables
BOOK (DDC,TITLE,ISBN,AUTH_NAME)
AUTHOR (ID,DDC,AUTH_NAME)

AUTH_NAME column in BOOK's table contains NULL for all records.

I want update table BOOK's column AUTH_NAME from AUTHOR's column in AUTHOR table where DDC numbers match. DDC is not unique in AUTHOR's table. I want to update BOOK table with the second matching AUTH_NAME. Rest should be ignored.


I used the query:
update BOOK
set AUTH_NAME = (
select AUTHOR.AUTH_NAME FROM AUTHOR
WHERE AUTHOR.DDC = BOOK.DDC AND rownum = 1);

But this query is applicable only for the first occurance.
But I want to update table BOOK with second occurence.

Guide me for it please.

Thanks
Re: updating table [message #305293 is a reply to message #305289] Mon, 10 March 2008 05:54 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Define "second occurrence".

Post a test case and the result you want with this one.

Regards
Michel
Re: updating table [message #305299 is a reply to message #305293] Mon, 10 March 2008 06:05 Go to previous message
Frank Naude
Messages: 4502
Registered: April 1998
Senior Member
A function like this should help:

CREATE OR REPLACE FUNCTION all_authors(p_ddc NUMBER) RETURN varchar2
AS
  v_authors VARCHAR2(4000) := NULL;
BEGIN
  FOR c1 IN (SELECT * FROM author WHERE ddc = p_ddc) LOOP
    IF v_authors IS NULL THEN
       v_authors := c1.auth_name;
    ELSE
      v_authors := v_authors ||', '|| c1.auth_name;
    END IF;
  END LOOP;

  RETURN v_authors;
END;
/
Previous Topic: NO DATA FOUND exception
Next Topic: Rowcount with dynamic cursor
Goto Forum:
  


Current Time: Sat Dec 03 03:51:05 CST 2016

Total time taken to generate the page: 0.16567 seconds