Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Update one table based on another table and the Date column

Posted on 2014-11-13
5
Medium Priority
?
148 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 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

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
Ready to get certified? Check out some courses that help you prepare for third-party exams.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
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
Suggested Courses

578 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