Update Statment Help using JOINS?

Posted on 2007-10-05
Last Modified: 2010-03-20
Okay so I know how to select data from tables using Joins but how do I update whilst using this principle.

Eg.  I have 3 tables, 1: Stock, 2: StockToRecords, & 3: Records

I could do this in a select
SELECT records.recid from records
INNER JOIN StockToRecords ON Records.RecID = StockToRecords.RecID
INNER JOIN Stock ON StockToRecords.Partno = Stock.Partno
where Stock.Partno = 'banana'

So I can select based on the Stock table but how using this relation as above would I update a column in records using a where clause from stock?

Thanks in advance,

Question by:andrewmilner
    LVL 75

    Accepted Solution

    SET urColumn = newValue

    from records r
    INNER JOIN StockToRecords ON r.RecID = StockToRecords.RecID
    INNER JOIN Stock ON StockToRecords.Partno = Stock.Partno
    where Stock.Partno = 'banana'

    Author Comment

    Spot On!!!

    That's fantastic.  Thank you very much.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    Suggested Solutions

    Title # Comments Views Activity
    Space Delimited Sql File 4 59
    Access Database - Manually changing xid value 7 31
    SQL Select Query problems 10 36
    PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
    This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
    Migrating to Microsoft Office 365 is becoming increasingly popular for organizations both large and small. If you have made the leap to Microsoft’s cloud platform, you know that you will need to create a corporate email signature for your Office 365…
    how to add IIS SMTP to handle application/Scanner relays into office 365.

    761 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    11 Experts available now in Live!

    Get 1:1 Help Now