Search code examples
sqlsql-serversql-server-2008sql-updatesubquery

Updating a table in SQL Server with information from another table

I have a "claims" table. Each claim has a client attached to it which is represented by a few letters "ABED". For some reason, the actual clientID is nowhere to be found in the claims table so I want to fix this. The "Clients" table DOES contain both the code AND clientID. How do I run a query where I can update existing claims with the clientID based on the code?

UPDATE claims 
SET clientID = (SELECT clientID FROM clients WHERE claimID = code)

In the claims table, the code for the client is called "claimID". In the clients table it's called code.

Just to be clear, I did add the clientID column in the claims table, it is currently NULL and I'm looking to fill it with the clientID from the clients table.

Solution

  • Is this what you want?

    update cla
set clientid = (select t.clientid from clients cli where cli.code = cla.claimid)
from claims cla

    This searches for clients whose code matches the claimid of claims, and updates the corresponding clientid.