January 7, 2015 at 1:13 pm
I am somewhat new and for that please forgive me but I need to create this script and it is driving me crazy that I can't figure it out. So I have two tables and I need to copy the value (date) of one to the other one IF the value is null. So I have table a and table b, a has a setup_date and b (which has the nulls) have a begin_date. The keys for both tables are cust_code and proj_code.
Ideally it will check if its null and if its null then it will lookup the cust_code and proj_code (to match) and copy from a to b the date. Thank you for your help
January 7, 2015 at 1:29 pm
jrodriguez 62807 (1/7/2015)
I am somewhat new and for that please forgive me but I need to create this script and it is driving me crazy that I can't figure it out. So I have two tables and I need to copy the value (date) of one to the other one IF the value is null. So I have table a and table b, a has a setup_date and b (which has the nulls) have a begin_date. The keys for both tables are cust_code and proj_code.Ideally it will check if its null and if its null then it will lookup the cust_code and proj_code (to match) and copy from a to b the date. Thank you for your help
SELECT *
FROM TableB MyTarget --what i want to update
INNER JOIN TableA MySource --where my differnet data is
ON MyTarget.cust_code = MySource.cust_code
AND MyTarget.proj_code = MySource.proj_code
--don't update everything, just stuff that does not match
WHERE ISNULL(MyTarget.[begin_date],'1900-01-01') <> MySource.[setup_date]
UPDATE MyTarget -- just an alias to make my query easy to select TableA
SET MyTarget.[begin_date] = MySource.[setup_date]
FROM TableB MyTarget --what i want to update
INNER JOIN TableA MySource --where my differnet data is
ON MyTarget.cust_code = MySource.cust_code
AND MyTarget.proj_code = MySource.proj_code
--don't update everything, just stuff that does not match
WHERE ISNULL(MyTarget.[begin_date],'1900-01-01') <> MySource.[setup_date]
Lowell
January 7, 2015 at 1:29 pm
jrodriguez 62807 (1/7/2015)
I am somewhat new and for that please forgive me but I need to create this script and it is driving me crazy that I can't figure it out. So I have two tables and I need to copy the value (date) of one to the other one IF the value is null. So I have table a and table b, a has a setup_date and b (which has the nulls) have a begin_date. The keys for both tables are cust_code and proj_code.Ideally it will check if its null and if its null then it will lookup the cust_code and proj_code (to match) and copy from a to b the date. Thank you for your help
First of all, welcome to this forum. It's usually recommended to post table definitions and sample data along with your problem. You can find how to do it in the article linked in my signature. As you're new, I'll try my best to help you with what you posted.
For a start, you need to change the way you think. SQL is a declarative language not procedural. You tell it what you need to do and it will define how.
UPDATE b SET
begin_date = a.setup_date
FROM TableB b
JOIN TableA a ON b.cust_code = a.cust_code AND b.proj_code = a.proj_code
WHERE b.begin_date IS NULL
Basically, you're updating the column begin_date in table b with the correspondant setup_date in table a, only when begin_date is null.
If you have any questions, feel free to ask.
January 7, 2015 at 1:30 pm
-- this better not be a school homework problem
UPDATE b
SET begin_date = a.setup_date
from tableA a
JOIN tableB b ON a.cust_code = b.cust_code AND a.proj_code = b.proj_code
WHERE b.begin_date IS NULL
;
January 7, 2015 at 1:34 pm
You guys are awesome and no, it is NOT a school assignment (I wish, actually!). I got assigned to do some sql scripting (learned several years ago but haven't used it in about 7 years)I'm trying to get back on the swing of things. Thank you for the recommendations and help! you guys are awesome!!
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply