Thursday, 30 August 2018

Update Based on Values ​​from Another 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

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;

0 comments:

Post a Comment