Post reply

SQL UPDATE statement with a view

  • April 22, 2009 at 8:23 am

    #133402

    Hi there I am trying to update a field in our contact table. I have a view which contains all of the contacts that i need to run the update for, my question is:

    How do i update a contact table but only update the contacts that are in my custom view?

  • April 22, 2009 at 8:27 am

    #982221

    the view itself may be updatable...

    UPDATE MYVIEW SET ContactName='Bob' Where ContactId=1 And ContactName='BOBBY'

    can you tell us if that works? it really depends on the view...

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • April 22, 2009 at 8:33 am

    #982226

    Hi Lowell

    It isn't the view that i want to update, i want to update the contact table but only update the clients that appear in the view.

    I thought about doing a select statement with an update but im unsure of how to use joins in an update statement.

    David

  • April 22, 2009 at 8:42 am

    #982233

    that's the point...when a view is updatable, if you update the view, you are really updating the tables underneath the view.

    the view is just a stored select statement, but as an object in the database, if the underlying SQL statement makes each row unique, you can insert/update into a view, and the underlying tables get the changes.

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • April 22, 2009 at 8:45 am

    #982239

    Oh ok i see, so if i were to say:

    UPDATE MYVIEW SET FIELD1 = 1

    Would that update field1 for all records that are returned in view?

  • April 22, 2009 at 8:46 am

    #982241

    What Lowell is talking about is using the view to update the underlying table. You can learn more about updateable views in Books Online. If your view meets the criteria for an updateable view, you can use it directly to update the underlying table.

  • April 22, 2009 at 8:50 am

    #982243

    lame example, but it all works:

    USE TEMPDB

    CREATE TABLE MyContacts(contactid int identity(1,1) not null primary key,ContactName varchar(30),OTHERFIELD INT)

    insert into MyContacts(ContactName)

    SELECT 'Bugs Bunny' UNION ALL

    SELECT 'Homer Simpson' UNION ALL

    SELECT 'Rocky and Bullwinkle' UNION ALL

    SELECT 'Beavis and Butt-head' UNION ALL

    SELECT 'The Grinch'

    GO

    CREATE VIEW MyView As

    SELECT * FROM MyContacts Where ContactName LIKE 'B%'

    GO

    SELECT * FROM MyView

    UPDATE MyView SET ContactName = 'Bugs Bunny,ESQ' WHERE contactid=1

    INSERT INTO MyView(ContactName)

    SELECT 'Bart and Lisa Simpson'

    INSERT INTO MyView(ContactName)

    SELECT 'Moe The Bartender'

    update myview SEt OTHERFIELD=1

    SELECT * FROM MyView

    select * from MyContacts

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • April 22, 2009 at 9:03 am

    #982254

    Thank you for that Lowell

    That worked perfectly.

    Thanks again

  • April 22, 2009 at 11:14 am

    #982363

    glad it worked for you!

    I know i was surprised way back when when i found out views could be updated; I always thought of a view a s read only back then, and it's nice to find out extra functionalities like this that can really work to your advantage.

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • April 22, 2009 at 1:08 pm

    #982453

    Okay... I just have to throw this in..

    I just read last night that you can perform updates against inline table valued functions.. They are handled like views. (Today is a very special day. Can you say "parameterized view"? I knew you could. ) Can't wait to test this out tonight.

    __________________________________________________

    Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
    Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

Viewing 10 posts - 1 through 10 (of 10 total)

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