Solved

Update one table based on another table and the Date column

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

Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

Question has a verified solution.

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

Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

863 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

21 Experts available now in Live!

Get 1:1 Help Now