• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 346
  • Last Modified:

Update Table A from table B without removing old data from table A

I have 2 tables, Table A  & Table B

Table A

CREATE TABLE TABLE_A
(
USERID     CHAR(7),
LNAME      CHAR(30),
FNAME      CHAR(20),
ADDRESS  CHAR(50),
SHIRT       CHAR(10)
)

INSERT INTO TABLE_A  VALUES ('12345',' Quest','Johnny','312 Elm St','Small')
INSERT INTO TABLE_A  VALUES ('32123','Small','Babs','9405 Center',X-'Small')

USERID     LNAME                 FNAME        ADDRESS              SHIRT SIZE
12345       Quest                  Johnny         312 Elm St             Small
32123        Small                 Babs            9405 Center St       X-Small

Table B

CREATE TABLE TABLE_A
(
USERID     CHAR(7),
LNAME      CHAR(30),
FNAME      CHAR(20),
ADDRESS  CHAR(50)
)

INSERT INTO TABLE_A  VALUES ('12345','Quest','Johnny','123 Madison Ave')
INSERT INTO TABLE_A  VALUES ('98231','Bush','George','1209 Money Lane')

USERID     LNAME                 FNAME        ADDRESS
12345       Quest                 Johnny         123 Madiosn Ave
98231       Bush                   George        1209 Money Lane

I need to update table A to include all data from table B but not delete the extra fields or rows in table A that are not in table B

Table A after update:
USERID    LNAME            FNAME             ADDRESS                      SHIRT
12345     Quest              Johnny             123 Madiosn Ave            Small
98231      Bush               George            1209 Money Lane            Null
32123      Small              Babs               9405 Center St               X-Small            
0
a016691
Asked:
a016691
  • 2
1 Solution
 
JesterTooCommented:
I know of no way to perform both an insert and an update in a single query using MS SQL Server (other dbms's can), but you can do it with two queries easily enough... here they are:

Run the update query first (causes less work to be performed)...

UPDATE TABLE_A
   SET lname   = b.lname,
       fname   = b.fname,
       address = b.address
  FROM table_a a, table_b b
 WHERE a.userid  = b.userid


INSERT INTO table_a (userid, lname, fname, address)
   SELECT * FROM table_b
    WHERE not exists (select * from table_a where table_a.userid = table_b.userid)

HTH,
Lynn
                                                                                 
0
 
Brian CroweCommented:
INSERT INTO Table_A (USERID, LNAME, FNAME, ADDRESS)
SELECT Table_B.USERID, Table_B.LNAME, Table_B.FNAME, Table_B.ADDRESS
FROM Table_B
LEFT OUTER JOIN Table_A
     ON Table_B.USERID = Table_A.USERID
WHERE Table_A.USERID IS NULL
0
 
Anthony PerkinsCommented:
Here is your corrected script:
CREATE TABLE TABLE_A
(
USERID     CHAR(7),
LNAME      CHAR(30),
FNAME      CHAR(20),
ADDRESS  CHAR(50),
SHIRT       CHAR(10)
)

INSERT INTO TABLE_A  VALUES ('12345',' Quest','Johnny','312 Elm St','Small')
INSERT INTO TABLE_A  VALUES ('32123','Small','Babs','9405 Center','Small')

CREATE TABLE TABLE_B
(
USERID     CHAR(7),
LNAME      CHAR(30),
FNAME      CHAR(20),
ADDRESS  CHAR(50)
)

INSERT INTO TABLE_B  VALUES ('12345','Quest','Johnny','123 Madison Ave')
INSERT INTO TABLE_B  VALUES ('98231','Bush','George','1209 Money Lane')

Select * from table_A
Select * from table_B

Update      Table_A
Set      USERID = b.USERID,
      LNAME = b.LNAME,
      FNAME = b.FNAME,
      ADDRESS = b.ADDRESS
From      Table_B b
        Inner Join Table_A a On b.USERID = a.USERID

Insert Table_A (USERID, LNAME, FNAME, ADDRESS)
Select b.USERID, b.LNAME, b.FNAME, b.ADDRESS
From  Table_B b
          LEFT Join Table_A a On b.USERID = a.USERID
Where a.UserID Is Null

Select * from table_A

Drop table TABLE_A
Drop table TABLE_B
0
 
Anthony PerkinsCommented:
What am I missing here:

CREATE TABLE TABLE_A
(
USERID     CHAR(7),
LNAME      CHAR(30),
FNAME      CHAR(20),
ADDRESS  CHAR(50),
SHIRT       CHAR(10)
)

INSERT INTO TABLE_A  VALUES ('12345',' Quest','Johnny','312 Elm St','Small')
INSERT INTO TABLE_A  VALUES ('32123','Small','Babs','9405 Center','Small')

CREATE TABLE TABLE_B
(
USERID     CHAR(7),
LNAME      CHAR(30),
FNAME      CHAR(20),
ADDRESS  CHAR(50)
)

INSERT INTO TABLE_B  VALUES ('12345','Quest','Johnny','123 Madison Ave')
INSERT INTO TABLE_B  VALUES ('98231','Bush','George','1209 Money Lane')

Select * from table_A
Select * from table_B

INSERT INTO Table_A (USERID, LNAME, FNAME, ADDRESS)
SELECT Table_B.USERID, Table_B.LNAME, Table_B.FNAME, Table_B.ADDRESS
FROM Table_B
LEFT OUTER JOIN Table_A
     ON Table_B.USERID = Table_A.USERID
WHERE Table_A.USERID IS NULL

Select * from table_A

Drop table TABLE_A
Drop table TABLE_B

Here is the output:
USERID  LNAME                          FNAME                ADDRESS                                            SHIRT      
------- ------------------------------ -------------------- -------------------------------------------------- ----------
12345    Quest                         Johnny               312 Elm St                                         Small    
32123   Small                          Babs                 9405 Center                                        Small    

USERID  LNAME                          FNAME                ADDRESS                                            
------- ------------------------------ -------------------- --------------------------------------------------
12345   Quest                          Johnny               123 Madison Ave                                  
98231   Bush                           George               1209 Money Lane                                  

USERID  LNAME                          FNAME                ADDRESS                                            SHIRT      
------- ------------------------------ -------------------- -------------------------------------------------- ----------
12345    Quest                         Johnny               312 Elm St                                         Small    
32123   Small                          Babs                 9405 Center                                        Small    
98231   Bush                           George               1209 Money Lane                                    NULL

And this is what you were looking for:
USERID    LNAME            FNAME             ADDRESS                      SHIRT
12345     Quest              Johnny             123 Madiosn Ave            Small
98231      Bush               George            1209 Money Lane            Null
32123      Small              Babs               9405 Center St               X-Small  

Not quite the same is it?

As JesterToo suggested you simply cannot fulfill your requirements from your question with one SQL Statement.
0

Featured Post

NEW Veeam Backup for Microsoft Office 365 1.5

With Office 365, it’s your data and your responsibility to protect it. NEW Veeam Backup for Microsoft Office 365 eliminates the risk of losing access to your Office 365 data.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now