Simple update query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Simple update query

Author

Topic

Topaz
Posting Yak Master

199 Posts

Posted - 2012-08-20 : 06:53:38

It's been a while since I posted on here, but I know its the place to come for answers.

My SQL database is made up of multiple tables. I need to update a single field from one table to another. Here is my scenario:

Table1 has two fields - AccountID and SICCODE.
Table2 also has a field called AccountID and SIC4Desc (linked by AccountID).

I need to copy the information from Table2's SIC4Desc to Table1's SICCODE where ACCOUNTID matches the ACCOUNTID of Table1

Any help on writing the query is much appreciated.

JT

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2012-08-20 : 06:56:08

[code]UPDATE
t1
SET
t1.SICCODE = t2.SIC4Desc
FROM
Table1 t1
IINNER JOIN
Table2 t2
ON
t1.AccountID = t2.AccountID[/code]

How to ask: http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Topaz
Posting Yak Master

199 Posts

Posted - 2012-08-20 : 07:01:44

Thanks for the solution however the T1 and T2 part is confusing me.

Table 1 is called: Account
Table 2 is called: Account_1to1

Where would I put the table names?

JT

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-08-20 : 07:16:07

[code]UPDATE
t1
SET
t1.SICCODE = t2.SIC4Desc
FROM
Account AS t1
IINNER JOIN
Account_1to1 AS t2
ON
t1.AccountID = t2.AccountID[/code]t1 and t2 are aliases for the the tables. The AS keyword that I added is optional, but that makes it more readable. Once you use an alias for a table, everywhere else in that SQL statement, you can (and must) refer to that table using that alias.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2012-08-20 : 07:39:31

Please note that the query above only updates the Accounts that have a match (inner join), and thus leave the old value intact of there is no match.
If you want to update the old value to NULL if there is no match, you have to use an outer join.

UPDATE	a
SET	a.SICCODE = (SELECT TOP(1) SIC4Desc FROM dbo.Account_1to1 AS x WHERE x.AccountID = a.AccountID)
FROM	dbo.Account AS a


UPDATE		t1
SET		t1.SICCODE = t2.SIC4Desc 
FROM		dbo.Account AS t1
LEFT JOIN	dbo.Account_1to1 AS t2 ON t2.AccountID = t1.AccountID

N 56°04'39.26"
E 12°55'05.63"

Topaz
Posting Yak Master

199 Posts

Posted - 2012-08-20 : 07:55:07

If I wanted to update a few accountid's would I modify the query like this:

UPDATE
t1
SET
t1.SICCODE = t2.SIC4Desc
FROM
Account AS t1
IINNER JOIN
Account_1to1 AS t2
ON
t1.AccountID = t2.AccountID

where t1.accountid = 'AUJU4444'

(I didn't want to run this query and update the whole table)

JT

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-08-20 : 08:16:20

Yes, that would update only rows that have accountid = AUJU4444. You can replace the UPDATE..SET part with a SELECT as shown below to see what the current values in the table are and what the SICCODE is going to be updated to before you pull the trigger to do the update.

--UPDATE t1
--SET    t1.SICCODE = t2.SIC4Desc
SELECT t1.*,t2.SIC4Desc
FROM   Account AS t1
       INNER JOIN Account_1to1 AS t2
            ON  t1.AccountID = t2.AccountID
WHERE  t1.accountid = 'AUJU4444'

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources