?
Solved

Update one table based on another table and the Date column

Posted on 2014-11-13
5
Medium Priority
?
147 Views
Last Modified: 2014-11-13
Hello Experts,

I have two SQL Tables that look like this:

Tbl_1:

Date    Type    Num
1/20      A        10
1/20      B        13
1/21      A        16
1/21      C        11
1/21      D        22


Tbl_2:
Date    Type    NumAdjust
1/20      A        -3
1/20      B        4
1/21      D        2

I would like to update Tbl 1 using the values in Tbl 2 so that Tbl_1 now looks like this:.

Tbl_1 updated
Date    Type    Num
1/20      A        7
1/20      B       17
1/21      A       16
1/21      C        11
1/21      D        24

I am unsure of the TSQL syntax to do this but think it must be similar to this:
Update Tbl_1
Set Tbl_1.Num = Tbl_1.Num + Tbl_2. NumAdjust
On Tbl_1.Date = Tble_2.Date and Tbl_1.Type = Tbl_2.Type

Does anyone know how to do this?

Thanks!
0
Comment
Question by:Saxitalis
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
5 Comments
 
LVL 11

Accepted Solution

by:
John_Vidmar earned 2000 total points
ID: 40441438
UPDATE	a
SET	Num	=	a.Num + b.NumAdjust
FROM	Tbl_1	a
JOIN	Tbl_2	b	ON	a.Date = b.Date
			AND	a.Type = b.Type

Open in new window

0
 
LVL 18

Expert Comment

by:JR2003
ID: 40441445
With SQL Server you can use a FROM in your UPDATE statement. To do it you write a SELECT query that returns the data as you want it then just delete the SELECT and add an UPDATE of the alias used in the FROM clause:
Step 1
SELECT T1.Type,
       T1.Num = T1.Num + T2 NumAdjust 
  FROM Tbl_1 T1
 INNER JOIN Tble_2 T2
         ON T2.Type = T1.Type

Open in new window


Step 2:
Then simply turn it into an UPDATE:
UPDATE T1
   SET T1.Num = T1.Num + T2 NumAdjust 
  FROM Tbl_1 T1
 INNER JOIN Tble_2 T2
         ON T2.Type = T1.Type

Open in new window

0
 

Author Comment

by:Saxitalis
ID: 40441636
Mr. John Vidmar,

When I run your code:

UPDATE      a
SET      Num      =      a.Num + b.NumAdjust
FROM      Tbl_1      a
JOIN      Tbl_2      b      ON      a.Date = b.Date
                  AND      a.Type = b.Type


I get this error:

Msg 295, Level 16, State 3, Line 1
Conversion failed when converting character string to smalldatetime data type.

Do I need to explicitly Convert Datatypes here??

Thanks,
John (saxitalis)
0
 
LVL 18

Expert Comment

by:JR2003
ID: 40441643
Sounds like you have the Date column on one table defined as a varchar and on the other table as a smalldatetime.
You will need to to a CONVERT so that SQL server will be able to compare the two together.
0
 

Author Closing Comment

by:Saxitalis
ID: 40441695
That was it - Thanks!
0

Featured Post

Get real performance insights from real users

Key features:
- Total Pages Views and Load times
- Top Pages Viewed and Load Times
- Real Time Site Page Build Performance
- Users’ Browser and Platform Performance
- Geographic User Breakdown
- And more

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

777 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question