Post reply

Multiple values in same column.

  • March 17, 2011 at 7:27 am

    #389072

    hi to all, I created following table

    create table book(id integer, author varchar(20))

    In this, each book may have 1 or more authors.

    My question is that, how can insert more value to author column like as follows.

    id author

    1 pooja merry james

    2 robert stephen

    Thanks & regerds,

    Pooja

  • March 17, 2011 at 7:43 am

    #1299941

    you need 3 tables.

    Books

    Authors

    BooksAuthors

    BooksAuthors will contain the id for the book and the author. You can add as many lines (authors) as required there for each book.

  • March 18, 2011 at 1:10 am

    #1300328

    Hi Pooja

    Ninja is correct, your tables should be properly normalised. This solution does not scale well, have you given any thought as to how you are going pull the values back out of the string value as there aren’t any delimiters in the author columns either (it is unlikely that all your authors have single names).

    However best practice aside, the problem you will have is that the Varchar(20) you are using will not allow you to add another name longer than 2 characters into the first row and 5 in the second, if you persist with this approach you will need to increase the length on the column to something more suitable. You can then use this command to add the name John to the authors in Book id 1

    DECLARE @BookId int, @NewAuthor varchar(50)

    SET @BookId = 1

    SET @NewAuthor = 'John'

    UPDATE Book

    SET Author = Author + ' ' + @NewAuthor

    WHERE id = @BookId

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply