Solved

updating sql table where other tables match

Posted on 2012-12-26
11
131 Views
Last Modified: 2013-01-01
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
Comment
Question by:solarissf
  • 6
  • 3
  • 2
11 Comments
 
LVL 11

Accepted Solution

by:
David Kroll earned 350 total points
ID: 38721538
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
 

Author Comment

by:solarissf
ID: 38721542
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
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 38721553
update or delete + insert the rows?
0
 
LVL 11

Expert Comment

by:David Kroll
ID: 38721558
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
 

Author Comment

by:solarissf
ID: 38721573
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
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 
LVL 11

Expert Comment

by:David Kroll
ID: 38721585
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
 

Author Comment

by:solarissf
ID: 38721767
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
 
LVL 142

Assisted Solution

by:Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3] earned 150 total points
ID: 38721799
0
 

Author Comment

by:solarissf
ID: 38721807
thanks , i'll check it out
0
 

Assisted Solution

by:solarissf
solarissf earned 0 total points
ID: 38724671
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
 

Author Closing Comment

by:solarissf
ID: 38734232
added to solution
0

Featured Post

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Sql query to Stored Procedure 6 38
Stored Procedure 2 47
Dimension table indexes 8 12
Managing Columnstore Indexes 2 17
I have written a PowerShell script to "walk" the security structure of each SQL instance to find:         Each Login (Windows or SQL)             * Its Server Roles             * Every database to which the login is mapped             * The associated "Database User" for this …
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Windows 10 is mostly good. However the one thing that annoys me is how many clicks you have to do to dial a VPN connection. You have to go to settings from the start menu, (2 clicks), Network and Internet (1 click), Click VPN (another click) then fi…
Both in life and business – not all partnerships are created equal. As the demand for cloud services increases, so do the number of self-proclaimed cloud partners. Asking the right questions up front in the partnership, will enable both parties …

920 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

14 Experts available now in Live!

Get 1:1 Help Now