Update a joined table if value present

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 a joined table if value present

Author

Topic

pwvailla
Starting Member

31 Posts

Posted - 2010-09-20 : 11:05:26

I am converting an old project management system (ARTEMIS) into Primavera. It was supposed to go Oracle (which I know) but is on SQL Server 2005 until we get funded in 2011.

I need to UPDATE fields in a joined table if the value is present.

Example:

Update Table_a(source table)
use table_b (they are joined by order_no)
set
table_b.order_no = table_a.order_no if table_a.order_no PRESENT (ie NOT NULL)

Ultimately, table a contains weekly updates and table b is a table that will contains the lastest (current) update values.

rohitvishwakarma
Posting Yak Master

232 Posts

Posted - 2010-09-20 : 11:09:41

UPDATE S
SET S_b.order_no = N.order_no
FROM table_b S
INNER JOIN Table_a N
ON S.order_no = N.order_no
AND table_a.order_no IS NOT NULL

slimt_slimt
Aged Yak Warrior

746 Posts

Posted - 2010-09-20 : 11:13:39

is you can post DDL of these two tables it would be best.

anyways, try this:


update a
set a.column_a = b.column_b
from
tableA as a
join TableB as b
on a.order_no = b.order_no
where
a.order_no is not null
and len(a.order_no) > 0

keep in mind that column_A and column_B (the one that you are trying to update) must be same datatype and also make sure that the update makes sense :)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-09-21 : 12:11:17

why should you include this where check? you people are doing INNER JOIN so update will happen only if table_a.order_no is present rite?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

pwvailla
Starting Member

31 Posts

Posted - 2010-09-21 : 18:44:48

Question:
I am new to TSQL. I am trying to update a joined table and set values into the joined table if the values are present in the source table. I am converting some old SQL from a product called ARTEMIS and the OLD syntax is as follows:

(joined table based on order_no field in each table and all fields are similar datatypes)

set in table_A A (source)
use table_B B
set if order_no exists in table_B
B.busunit = A.busunit if A.busunit present
B.custname = A.custname if A.custname present
B.model_code = A.model_code if A.model_code present
etc...

How would you structure something like this using TSQL 2005?

quote:
Originally posted by visakh16

why should you include this where check? you people are doing INNER JOIN so update will happen only if table_a.order_no is present rite?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2010-09-21 : 19:08:04

What is the question you are actually asking? I answered this question in your duplicate thread, which is the same answer as rohit's. Do they not work? If not, what is the problem? Please answer slimt's question. He's asking you to post the table structures of the two tables and how they link to each other.

Jim

Everyday I learn something that somebody else already knew

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-09-23 : 12:58:30

quote:
Originally posted by pwvailla

Question:
I am new to TSQL. I am trying to update a joined table and set values into the joined table if the values are present in the source table. I am converting some old SQL from a product called ARTEMIS and the OLD syntax is as follows:

(joined table based on order_no field in each table and all fields are similar datatypes)

set in table_A A (source)
use table_B B
set if order_no exists in table_B
B.busunit = A.busunit if A.busunit present
B.custname = A.custname if A.custname present
B.model_code = A.model_code if A.model_code present
etc...

How would you structure something like this using TSQL 2005?

quote:
Originally posted by visakh16

why should you include this where check? you people are doing INNER JOIN so update will happen only if table_a.order_no is present rite?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

something like


UPDATE b
SET b.busunit = COALESCE(a.busunit,b.busunit)
b.custname = COALESCE(a.custname,b.custname)
b.model_code = COALESCE(a.model_code,b.model_code)
FROM table_B b
INNER JOIN table_A a
ON a.order_no = b.order_no

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources