Update statement for SQl Server is not working

Question

0.00/5 (No votes)

See more:

I have a SQL Server 2005. I have two tables. Table1 is the old table and table2 is the updated table. I am trying to update table1 with table2 data but my update code is wrong. What I have in table1 is just Firstname , Lastname, Email, accesslevel. Table2 has the updated First, Last and Email. I just want to update the Firstname, Lastname and Email without loosing the accesslevel in table1. Also the tables have userid's in them also. I was trying to update table1 with table2 data depending on the userid. Here is my code.

SQL

UPDATE [Hot].[dbo].[Table1] SET [Firstname], [Lastname], [Email] = 
Table1.userid
FROM Table2
WHERE [Hot].[dbo].[Table2].userid = Table2.userid

Posted 30-Jun-14 4:26am

Computer Wiz99

Add a Solution

1 solution

Add a Solution

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Peter Leow · Accepted Answer · 2014-06-30T04:32:00

Solution 1

Try this:

SQL

UPDATE table1
SET
    table1.firstname = table2.firstname,
    table1.lastname = table2.lastname,
    table1.email = table2.email
FROM
    table1 JOIN table2
ON
    table1.userid=table2.userid

Posted 30-Jun-14 4:32am

Peter Leow

Updated 30-Jun-14 4:33am

v3

Comments

Computer Wiz99 30-Jun-14 10:35am

Peter Leow, Thanks for the code but will this update the right recodes in the table that matches the userid and accesslevel?

Peter Leow 30-Jun-14 11:53am

yes, for records that have the same userid in both tables. The accesslevel will not be changed.

Computer Wiz99 30-Jun-14 16:02pm

Thanks.

Update statement for SQl Server is not working

1 solution

Solution 1

Add your solution here

Preview 0

Update statement for SQl Server is not working

1 solution

Solution 1

Add your solution here

Preview 0

Existing Members

...or Join us