UPDATE table 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

UPDATE table query

Author

Topic

Naught
Starting Member

2 Posts

Posted - 2011-07-21 : 03:20:48

Hello everyone.

I'm trying to move a whole bunch of data from Table1 to Table2, using the UPDATE function, but only under special circumstances.

I wish to move all available addressId's from Table1 to Table2, but only if there isn't an addressId in Table2 already.

Example data.

Table1

Table1_ID(PK) -- ADDRESSID(FK)
------------------------------
1----------------100
2----------------200
3----------------(null)
4----------------300
5----------------400

Table2

Table2_ID(PK) -- Table1_ID(FK) -- ADDRESSID(FK)
----------------------------------------------
10----------------1----------------100
20----------------2----------------600
30----------------2----------------(null)
40----------------2----------------(null)
50----------------3----------------(null)
60----------------4----------------(null)
70----------------5----------------700

(Note that the addressId in Table2 and it's referenced row in Table1 doesn't necesarily have to be the same. They can have independant adresses. All I wish is to fill the blank spots in Table2 if there's an available adressid in the row it's refering to in Table1)

Desired result:

Table2

Table2_ID(PK) -- Table1_ID(FK) -- ADDRESSID(FK)
----------------------------------------------
10----------------1----------------100
20----------------2----------------600
30----------------2----------------200
40----------------2----------------200
50----------------3----------------(null)
60----------------4----------------300
70----------------5----------------700

I've tried to display the relationship between the tables above, but to clarify;

Table2 has a FK to Table1. Multiple records in Table2 may point at the same record in Table1. Table1 doesn't know anything about Table2. Both tables have a FK to an address table.

I hope it makes sense, otherwise I'm sorry to have wasted your time :)

I've tried something sorta like this.

UPDATE TABLE2 T2
SET T2.ADDRESSID = T1.ADDRESSID
WHERE T2.ADDRESSID IS NULL AND EXISTS
(SELECT T1.ADDRESSID
FROM Table1 T1
WHERE T1.ADDRESSID IS NOT NULL
AND T1.TABLE1_ID = T2.TABLE1_ID)

Compiler throws this:

Invalid identifier T1.ADDRESSID. (Bold'd above)

I'm sure it's out of scope, since the T1 alias is defined inside the select block.

Anyways... I've chipped away at this for a couple of hours now, but with no luck. It would'nt surprise me at all if what I'm looking for is a 1 liner or something :)

Have a nice day :)

/LW

Edit: typos

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2011-07-21 : 03:34:09

Try this:

update t2
set ADDRESSID = t1.ADDRESSID
from TABLE2 t2
join TABLE1 t1 on t1.TABLE1_ID = t2.TABLE1_ID
where t2.ADDRESSID IS NULL

No, you're never too old to Yak'n'Roll if you're too young to die.

Naught
Starting Member

2 Posts

Posted - 2011-07-21 : 04:17:45

quote:
Originally posted by webfred

Try this:

update t2
set ADDRESSID = t1.ADDRESSID
from TABLE2 t2
join TABLE1 t1 on t1.TABLE1_ID = t2.TABLE1_ID
where t2.ADDRESSID IS NULL

No, you're never too old to Yak'n'Roll if you're too young to die.

Thank you Webfred, for the fast response.

The above looks really simple and clean. Does it take into account that the addressid in table1 might be null?

If not, would an additional "AND t1.ADDRESSID IS NOT NULL" to the where clause fix that?

I'll try out this query either way :]

Thanks again.

/LW

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2011-07-21 : 04:25:08

Yes you can add that to avoid meaningless updates

No, you're never too old to Yak'n'Roll if you're too young to die.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources