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
Solved

Update one table based on another table and the Date column

Posted on 2014-11-13
5
142 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
  • 2
  • 2
5 Comments
 
LVL 11

Accepted Solution

by:
John_Vidmar earned 500 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

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

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

Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
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.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

808 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