Combining two 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

SQL Server 2005 Forums

Transact-SQL (2005)

Combining two UPDATE QUERY

Author

Topic

cmorga23
Starting Member

10 Posts

Posted - 2007-12-11 : 17:47:56

I have two update query's. Could someone tell me how to combine them into one statement. See below

UPDATE ___________

SET ___________ = (_____________)(sub query same for both)

Where _________ is NUll

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-12-11 : 17:59:27

Yes, use a INNER JOIN as explained to you before.

UPDATE x
SET x.Col1 = y.v1,
x.Col2 = y.v2
FROM Table1 AS x
INNER JOIN (SELECT ... ) AS y ON y.PkCol = x.PkCol

E 12°55'05.25"
N 56°04'39.16"

cmorga23
Starting Member

10 Posts

Posted - 2007-12-11 : 18:02:26

THANKS ALOT!!!!!

cmorga23
Starting Member

10 Posts

Posted - 2007-12-12 : 11:40:37

INNER JOIN WILL NOT WORK FOR THIS I DON'T BELIEVE.

How do I combine the following two update query's????

Example:

UPDATE py_per_stu_email SET py_eml_un = (SELECT py_email_un FROM master WHERE master.id = py_per_stu_email.id)
Where py_eml_un ISNULL

UPDATE py_per_stu_email SET py_eml_default = (SELECT py_email_un FROM master WHERE master.id = py_per_stu_email.id)
Where py_eml_default ISNULL

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2007-12-12 : 11:49:44

Try this:-
UPDATE p
SET p.py_eml_un =COALESCE(p.py_eml_un,m.py_email_un),
p.py_eml_default =COALESCE(p.py_eml_default,m.py_email_un)
FROM py_per_stu_email p
INNER JOIN master m
ON m.id = p.id
WHERE p.py_eml_un IS NULL
OR p.py_eml_default IS NULL

cmorga23
Starting Member

10 Posts

Posted - 2007-12-12 : 15:29:18

Any other idea's???This does not work.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-12-13 : 01:24:01

quote:
Originally posted by cmorga23

Any other idea's???This does not work.

What do you mean by "this does not work?"
Did you get error?

Madhivanan

Failing to plan is Planning to fail

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-12-13 : 04:35:43

ISNULL is not a valid function. It should say "IS NULL".

UPDATE		p
SET		p.py_eml_un = COALESCE(m.py_email_un, p.py_eml_un),
		p.py_eml_default = COALESCE(m.m.py_email_un, p.py_eml_un)
FROM		py_per_stu_email AS p
LEFT JOIN	master AS m ON m.id = p.id

E 12°55'05.25"
N 56°04'39.16"

cmorga23
Starting Member

10 Posts

Posted - 2007-12-13 : 11:40:02

This is not the correct way to combine these two update queries. At least that is what my boss said. Thanks for the help. The tables are from two different databases. Not sure if that helps

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2007-12-13 : 12:53:39

then refer tables using{dbname}..TableName

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources