The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.
Course Of The Month
5 days, 20 hours left to enrollBecome a Premium Member and unlock a new, free course in leading technologies each month.
Solved
updating sql table where other tables match
Posted on 2012-12-26
hi guys,
I have the following statement:
This creates a new table putting together pieces of 2 tables where 2 conditions exist.
Now I that the table exists... the second time I run this I just want to update the values.
Can someone please help me change this query from a create new table... to update the existing table.
Appreciate it
thanks
I have the following statement:
This creates a new table putting together pieces of 2 tables where 2 conditions exist.
Now I that the table exists... the second time I run this I just want to update the values.
Can someone please help me change this query from a create new table... to update the existing table.
Appreciate it
thanks
SELECT
dbo.PositionsDB.Account_ID, dbo.PositionsDB.Position_ID, dbo.AccountsDB.Account_Number,
dbo.AccountsDB.Account_Description, dbo.PositionsDB.COB_Date, dbo.PositionsDB.Qty_Net,
dbo.PositionsDB.Exchange, dbo.PositionsDB.Product, dbo.PositionsDB.Contract_Date,
dbo.PositionsDB.Option_Type, dbo.PositionsDB.Strike,
dbo.PositionsDB.Settlement_Price
INTO dbo.DailyPositions_JEFF
FROM dbo.PositionsDB
INNER JOIN dbo.AccountsDB
ON dbo.PositionsDB.Account_ID = dbo.AccountsDB.Account_ID
WHERE dbo.AccountsDB.Client_ID = '418bb7b8-0180-4a5a-99f6-ce52e60e694f'
AND [COB_Date] = '20121219'
[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
-
6
3
2
11 Comments
IF NOT EXISTS
( SELECT [name]
FROM sys.tables
WHERE [name] = 'DailyPositions_JEFF'
)
BEGIN
SELECT
dbo.PositionsDB.Account_ID
dbo.AccountsDB.Account_Des
dbo.PositionsDB.Exchange, dbo.PositionsDB.Product, dbo.PositionsDB.Contract_D
dbo.PositionsDB.Option_Typ
dbo.PositionsDB.Settlement
INTO dbo.DailyPositions_JEFF
FROM dbo.PositionsDB
INNER JOIN dbo.AccountsDB
ON dbo.PositionsDB.Account_ID
WHERE dbo.AccountsDB.Client_ID = '418bb7b8-0180-4a5a-99f6-c
AND [COB_Date] = '20121219'
END
ELSE
BEGIN
--update statements here
END
( SELECT [name]
FROM sys.tables
WHERE [name] = 'DailyPositions_JEFF'
)
BEGIN
SELECT
dbo.PositionsDB.Account_ID
dbo.AccountsDB.Account_Des
dbo.PositionsDB.Exchange, dbo.PositionsDB.Product, dbo.PositionsDB.Contract_D
dbo.PositionsDB.Option_Typ
dbo.PositionsDB.Settlement
INTO dbo.DailyPositions_JEFF
FROM dbo.PositionsDB
INNER JOIN dbo.AccountsDB
ON dbo.PositionsDB.Account_ID
WHERE dbo.AccountsDB.Client_ID = '418bb7b8-0180-4a5a-99f6-c
AND [COB_Date] = '20121219'
END
ELSE
BEGIN
--update statements here
END
as I'm am completed new to this... the section where you have
--update statements here
Can you show me 1 example of updating 1 column with my inner join statements
I'm stuck there as well
thanks soo much
--update statements here
Can you show me 1 example of updating 1 column with my inner join statements
I'm stuck there as well
thanks soo much
UPDATE dbo.DailyPositions_JEFF
SET
Settlement_Price = 0
FROM dbo.DailyPositions_JEFF
INNER JOIN dbo.AccountsDB
ON dbo.DailyPositions_JEFF.Ac
ANDdbo.AccountsDB.Client_I
AND [COB_Date] = '20121219'
SET
Settlement_Price = 0
FROM dbo.DailyPositions_JEFF
INNER JOIN dbo.AccountsDB
ON dbo.DailyPositions_JEFF.Ac
ANDdbo.AccountsDB.Client_I
AND [COB_Date] = '20121219'
i tried this... still not working... at least for the 1 column
UPDATE dbo.DailyPositions_JEFF
SET
Settlement_Price = dbo.PositionsDB.Settlement_Price
FROM dbo.DailyPositions_JEFF
INNER JOIN dbo.AccountsDB
ON dbo.DailyPositions_JEFF.Account_ID = dbo.AccountsDB.Account_ID
UPDATE dbo.DailyPositions_JEFF
SET
Settlement_Price = dbo.PositionsDB.Settlement
FROM dbo.DailyPositions_JEFF
INNER JOIN dbo.PositionsDB
ON dbo.DailyPositions_JEFF.Ac
SET
Settlement_Price = dbo.PositionsDB.Settlement
FROM dbo.DailyPositions_JEFF
INNER JOIN dbo.PositionsDB
ON dbo.DailyPositions_JEFF.Ac
i'm losing my mind here.
I'm not sure if I found source or not.
Lets assume that the table columns are there but there is not data in dbo.DailyPositions_JEFF.
I am trying to populate these columns
dbo.PositionsDB.Account_ID
dbo.AccountsDB.Account_Des
dbo.PositionsDB.Exchange, dbo.PositionsDB.Product, dbo.PositionsDB.Contract_D
dbo.PositionsDB.Option_Typ
dbo.PositionsDB.Settlement
. So using your examples I just have to UPdate the columns. This is the part I'm stuck on .
to make things a bit more confusing... and maybe the source of the problem
Account_ID is a unique identifier from AccountsDB
and Position_ID is a unique identifier from PositionsDB
is this why your update statement is not working... am I supposed to be updating all of them a tthe same time instead of each column?
I'm not sure if I found source or not.
Lets assume that the table columns are there but there is not data in dbo.DailyPositions_JEFF.
I am trying to populate these columns
dbo.PositionsDB.Account_ID
dbo.AccountsDB.Account_Des
dbo.PositionsDB.Exchange, dbo.PositionsDB.Product, dbo.PositionsDB.Contract_D
dbo.PositionsDB.Option_Typ
dbo.PositionsDB.Settlement
. So using your examples I just have to UPdate the columns. This is the part I'm stuck on .
to make things a bit more confusing... and maybe the source of the problem
Account_ID is a unique identifier from AccountsDB
and Position_ID is a unique identifier from PositionsDB
is this why your update statement is not working... am I supposed to be updating all of them a tthe same time instead of each column?
Active 4 days ago
would this article help?
http://www.experts-exchange.com/Database/Miscellaneous/A_1517-UPDATES-with-JOIN-for-everybody.html
http://www.experts-exchange.com/Database/Miscellaneous/A_1517-UPDATES-with-JOIN-for-everybody.html
Featured Post
To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.
Question has a verified solution.
If you are experiencing a similar issue, please ask a related question
Suggested Solutions
| Title | # Comments | Views | Activity |
|---|---|---|---|
| Re-appearing SQL Server Agent jobs | 7 | 46 | |
| How to add a suffix to a value in a column based on the value in another column | 4 | 36 | |
| SQL works but want to get the XML node data separately | 11 | 56 | |
| Cannot get group page break to work in SSRS 2008R2. | 14 | 38 |
Audit has been really one of the more interesting, most useful, yet difficult to maintain topics in the history of SQL Server.
In earlier versions of SQL people had very few options for auditing in SQL Server. It typically meant using SQL Trace …
There have been several questions about Large Transaction Log Files in SQL Server 2008, and how to get rid of them when disk space has become critical. This article will explain how to disable full recovery and implement simple recovery that carries…
Attackers love to prey on accounts that have privileges.
Reducing privileged accounts and protecting privileged accounts therefore is paramount. Users, groups, and service accounts need to be protected to help protect the entire Active Directory …
Suggested Courses
Course of the Month5 days, 20 hours left to enroll
734 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.