Post reply

SET values to same as ID from another table, WHERE values match in another column - Is returning same value for all records, please help

  • May 13, 2010 at 10:23 am

    #235045

    Hi

    I am trying to set the values of a field (dbo.temp_dentist.clinic_id - not a pk) to the same as the PK in the dbo.Clinics table, where the clinic_ref and clinicref fields from the two table match.

    The query below runs, however it updates all the dbo.temp_dentist.clinic_id values to the first clinic_id from the dbo.clinics table. Am thinking it's something to do with one of the WHERE clauses or the JOIN but I am unsure. Any help would be massively appreicated.

    update dbo.temp_dentist

    set dbo.temp_dentist.clinic_id = dbo.Clinics.clinic_id

    from dbo.Clinics

    where dbo.temp_dentist.clinic_ref in (select dbo.Clinics.clinicref

    from dbo.Clinics

    INNER JOIN dbo.temp_dentist on dbo.temp_dentist.clinic_ref = dbo.Clinics.clinicref

    where dbo.temp_dentist.clinic_ref = dbo.Clinics.clinicref)

  • May 13, 2010 at 10:35 am

    #1167046

    First, if you're going to use the FROM clause in an UPDATE statement, you MUST include the table being updated.

    Since the field being updated is coming from a PK in the clinic table, you can change the update to this:

    UPDATE dbo.temp_dentist

    SET dbo.temp_dentist.clinic_id = dbo.Clinics.clinic_id

    FROM dbo.temp_dentist

    JOIN dbo.Clinics

    ON dbo.temp_dentist.clinic_ref = dbo.Clinics.clinicref

    If you need help beyond this, please see the first link in my signature for how to get help faster.

    Wayne
    Microsoft Certified Master: SQL Server 2008
    Author - SQL Server T-SQL Recipes

    If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
    Links:
    For better assistance in answering your questions
    Performance Problems
    Common date/time routines
    Understanding and Using APPLY Part 1 & Part 2

  • May 13, 2010 at 10:42 am

    #1167054

    Hey Wayne

    Thank you very much for your quick response. That did the trick.

    You're my hero.

    Many thanks

  • May 13, 2010 at 10:44 am

    #1167055

    Also, a lot of times when people do things like this they would be much better off not updating tables ... it is very possible it would be much better to leave the clinic_id where it is and simply join on the dbo.Clinics table in whatever query needs to return that information.

    └> bt


    Forum Etiquette: How to post data/code on a forum to get the best help[/url]

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

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