CODEWITHSUNDEEP
sqlsql-server-2008
DaveLeGO
DaveLeGO

Reputation: 87

SQL Server 2008 - Perform an update according to values in an other table

I have a really blocking problem with one of my SQL request. My problem is the following : I have a first table (a temporary table) which contains some users and their names, adresses, phone numbers, ... I have a second table (a "real" one) which contains informations about some commands. A command is related to a user, but it's a bit complicated. For more clarity, here a sample of the definition of my tables :

USER
ID          char(8)
LastName    nvarchar(30)
FirstName   nvarchar(30)
PostCode    nvarchar(10)
PhoneNumber nvarchar(20)
HomeAdress  nvarchar(20)

COMMAND
ID            char(6)
CMD_FirstName nvarchar(20)
CMD_LastName  nvarchar(20)
CMD_PostCode  nvarchar(10)
ID_User       char(8)

These two tables are logically related, but not in the practice ... In the COMMAND table, the field "ID_User" is usually missing. The purpose of my SQL resquest is to make an association between all the commands and the corresponding user by filling its ID_User field. For each command which the ID_User is null or empty, I want to retrieve the user by matching the fields last name, first name and post code. If this matching is strict (no doublon in the USER table, it's important),I can fill the ID_User field.

Can you please help me with this big and complicated request ? In advance, thanks !

PS : I'm using Microsoft SQL Server 2008 R2

Upvotes: 1

Views: 87

Answers (2)

RichardTheKiwi
RichardTheKiwi

Reputation: 107696

update c
set id_user = u.ID
from [user] u
join [command] c on c.cmd_firstname = u.firstname
                and c.cmd_lastname = u.lastname
                and c.cmd_postcode = u.postcode;

To exclude firstname/lastname/postcode combinations that match more than one user, you can pre-aggregate the user table and filter those out.

update c
set id_user = u.ID
from [command] c
join (select firstname, lastname, postcode, min(ID) ID
        from [user]
       group by firstname, lastname, postcode
      having count(*) = 1) u
   on c.cmd_firstname = u.firstname
  and c.cmd_lastname = u.lastname
  and c.cmd_postcode = u.postcode;

Upvotes: 4

AdamL
AdamL

Reputation: 13141

You need to assure, that nor [USER], neither [COMMAND] contain duplicate records for the fields that you want to use to map them. This contains correlated queries, and may not be very efficient for large tables.

update [command]
set ID_User = u.id
from [USER] u
join COMMAND c
    on u.LastName=c.CMD_LastName
    and u.FirstName = c.CMD_FirstName
    and u.PostCode = c.CMD_PostCode
where 1=1
    and not exists (
        select 
            LastName, FirstName, PostCode
        from [user]
        where 1=1
            and LastName=u.LastName
            and FirstName=u.FirstName
            and PostCode=u.PostCode
        group by 
            LastName, FirstName, PostCode
        having count(*)>1 
    )
    and not exists (
        select 
            CMD_LastName, CMD_FirstName, CMD_PostCode
        from COMMAND
        where 1=1
            and CMD_FirstName=c.CMD_FirstName
            and CMD_LastName=c.CMD_LastName
            and CMD_PostCode=c.CMD_PostCode
        group by 
            CMD_LastName, CMD_FirstName, CMD_PostCode
        having count(*)>1 
    )

Upvotes: 1

Related Questions