Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 138
  • Last Modified:

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


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'

Open in new window

0
solarissf
Asked:
solarissf
  • 6
  • 3
  • 2
3 Solutions
 
David KrollCommented:
IF NOT EXISTS
   (  SELECT [name]
      FROM sys.tables
      WHERE [name] = 'DailyPositions_JEFF'
   )
   BEGIN
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'
END
ELSE
BEGIN
--update statements here
END
0
 
solarissfAuthor Commented:
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
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
update or delete + insert the rows?
0
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

 
David KrollCommented:
UPDATE dbo.DailyPositions_JEFF
SET
Settlement_Price = 0
FROM dbo.DailyPositions_JEFF
INNER JOIN dbo.AccountsDB
ON dbo.DailyPositions_JEFF.Account_ID = dbo.AccountsDB.Account_ID
ANDdbo.AccountsDB.Client_ID = '418bb7b8-0180-4a5a-99f6-ce52e60e694f'
AND [COB_Date] = '20121219'
0
 
solarissfAuthor Commented:
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 

Open in new window

0
 
David KrollCommented:
UPDATE dbo.DailyPositions_JEFF
      SET
      Settlement_Price = dbo.PositionsDB.Settlement_Price
      FROM dbo.DailyPositions_JEFF
      INNER JOIN dbo.PositionsDB
      ON dbo.DailyPositions_JEFF.Account_ID = dbo.PositionsDB.Account_ID
0
 
solarissfAuthor Commented:
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.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


.  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?
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
0
 
solarissfAuthor Commented:
thanks , i'll check it out
0
 
solarissfAuthor Commented:
so the reason I couldnt get any results is because my code was incorrect.

I should have been using INSERT INTO since I started with a blank table. .. duhhh, my fault
That combined with your help has this issue solved.

Thanks!!!
0
 
solarissfAuthor Commented:
added to solution
0

Featured Post

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

  • 6
  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now