Solved

Update one table based on another table and the Date column

Posted on 2014-11-13
5
137 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

Suggested Solutions

Title # Comments Views Activity
SSRS  - Dropdown with Null 3 24
best counters for cpu high usage 3 25
Run SQL Server Proc from Access 11 28
Sql Server group by 10 25
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.
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Via a live example, show how to shrink a transaction log file down to a reasonable size.

813 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now