This Tuesday! Learn key insights about modern cyber protection services & gain practical strategies to skyrocket business:
- What it takes to build a cloud service portfolio
- How to determine which services will help your unique business grow
- Various use-cases and examples
updating sql table where other tables match
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'
Do more with
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
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?
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
Premium Content
You need an Expert Office subscription to comment.Start Free Trial