How to JOIN in an UPDATE - Across 2 tables

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

How to JOIN in an UPDATE - Across 2 tables

Author

Topic

dave_r33
Starting Member

4 Posts

Posted - 2014-10-15 : 11:40:41

Hi all,

My knowledge of SQL queries is patchy and basic. I'm sure this can be done, but the results I'm getting on google all seem to be for slightly different scenarios and dont seem to actually achieve what I want.

So I have a customers CRM DB that I need to run an update query on, affecting around 14,000 records. The fields used in the entity in question are split across two tables (thanks Microsoft). I need to update a field in one tabled based off of the result of a field in the other table.

So this is what I started with:

USE db1
UPDATE tbl2 SET field1 = '1' WHERE tbl1.CreatedOn < '2013-28-09 00:00:00.000';

This didn't work, SQL complained about being unable to bind tbl1.CreatedOn. I assume because it's in a different table to the one I'm updating.

I attempted a JOIN to the best of my limited SQL knowledge, thinking I could just shove the two tables together and it might be happy.

USE db1
INNER JOIN tbl1 ON tbl2.Id=tbl1.Id;
UPDATE tbl2 SET field1 = '1' WHERE tbl1.CreatedOn < '2013-28-09 00:00:00.000';

This also didn't work, complaining of syntax error near 'INNER'

I'm obviously missing something, but IU don't know what it is. Any ideas here?

Thanks in advance, Dave :)

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2014-10-15 : 11:50:16

The usual way in which people do this type of update is as follows:

UPDATE t2 SET
	field1 = '1'
FROM
	tbl1 t1 
	INNER JOIN tbl2 t2 ON t1.Id = t2.Id
WHERE
	t1.CreatedOn < '20130928';

However, this has some limitations, although in your specific example where you are setting the value in tbl2 to a constant, this should not matter.

dave_r33
Starting Member

4 Posts

Posted - 2014-10-15 : 12:04:43

quote:
Originally posted by James K

The usual way in which people do this type of update is as follows:
UPDATE t2 SET
field1 = '1'
FROM
tbl1 t1
INNER JOIN tbl2 t2 ON t1.Id = t2.Id
WHERE
t1.CreatedOn < '20130928';
However, this has some limitations, although in your specific example where you are setting the value in tbl2 to a constant, this should not matter.

Hi James,

Many thanks for that, It's approaching end of business hours, so I should be able to play with a bit less risk of taking the clients CRM offline by messing something up. I'll try your suggestion shortly and get back to you.

One thing, when you've got the FROM part, you have "FROM tbl1 t1" Is that a typo? Is it just meant to be "FROM tbl1"?

Regards, Dave.

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2014-10-15 : 12:42:19

t1 and t2 are table aliases. The full syntax includes an "AS", but that is optional, and can be omitted

UPDATE t2 SET
	field1 = '1'
FROM
	tbl1  AS t1 
	INNER JOIN tbl2  AS t2 ON t1.Id = t2.Id
WHERE
	t1.CreatedOn < '20130928';

dave_r33
Starting Member

4 Posts

Posted - 2014-10-15 : 12:47:25

Okay thanks James. Appreciate the help, will give it a try once I get home.

Regards, Dave.

dave_r33
Starting Member

4 Posts

Posted - 2014-10-15 : 17:49:03

Hi James,

Thanks again for your assistance there. Worked perfectly. I'll bookmark this for future reference as I'll undoubtedly need this again at some point.

Regards, Dave.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources