April 16, 2012 at 8:18 pm
Hi,
I have two tables that are of the same structure, the only difference is one is the main table and the other one stores the input from the application. I want to use the main table as a reference in my query in order to help my application.
Table 1 (Main Table)(dbo.MainMaster):
User ID Price
2 5401 62.00
2 6921 61.00
2 2215 49.00
3 3210 89.00
3 2425 99.00
Table 2 (Used By Application)(dbo.PriceTest):
User ID Price
2 5401 60.00
2 6921 61.55
2 2215 49.00
3 3210 91.00
3 2425 99.00
What I am trying to achieve is IF a record exists in Table 2 for the specific user on the same ID with the same price then to update the Price in column in Table 2 with the price for that ID and User in Table 1. I am not sure if I go the Update route or the select into route. But so Table 2 is getting populated through the following stored procedure that I want to modify in order to reference Table 1.
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER PROCEDURE [dbo].[AppInsert]
@User int
,@ID int
,@price money
AS
DELETE FROM dbo.PriceTest
WHERE User = @User
AND ID = @ID
INSERT INTO dbo.PriceTest
VALUES(@User,@ID,@price)
Any help will be appreciated
Thanks
April 16, 2012 at 10:51 pm
Try it...
Set ANSI_NULLS ON
Set QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[AppInsert] (
@User int
,@ID int
,@price money )
AS
Begin
IF EXISTS (SELECT Null FROM dbo.PriceTest WHERE User = @User AND ID = @ID)
UPDATE dbo.PriceTest SET Price = @price WHERE User = @User AND ID = @ID
ELSE
INSERT INTO dbo.PriceTest(User, ID, Price) VALUES(@User,@ID,@price)
End
April 17, 2012 at 8:44 am
Thanks for the response, but I am trying to reference dbo.MainMaster and update dbo.PriceTest with the values from dbo.MainMaster if the user is trying to insert values from the application that already exist in dbo.PriceTest
April 17, 2012 at 8:57 am
Please post DDL (CREATE TABLE statement) for the table(s) involved, sample data (series of INSERT INTO statements) for the table(s), sample updates to be used to update appropriate tables, expected results based on the sample data.
April 17, 2012 at 10:02 am
This is the create table queries: CREATE TABLE [dbo].[MainMaster](
[User] [int] NOT NULL,
[ID] [int] NOT NULL,
[Price] [money] NULL,
PRIMARY KEY CLUSTERED
(
[User] ASC,
[ID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
CREATE TABLE [dbo].[PriceTest](
[User] [int] NOT NULL,
[ID] [int] NOT NULL,
[Price] [money] NOT NULL,
CONSTRAINT [PK__PriceTest__696AB7F5] PRIMARY KEY CLUSTERED
(
[User] ASC,
[ID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
The sample data is posted in the first topic post.
April 17, 2012 at 10:05 am
Rank1_nj (4/17/2012)
This is the create table queries:CREATE TABLE [dbo].[MainMaster]([User] [int] NOT NULL,
[ID] [int] NOT NULL,
[Price] [money] NULL,
PRIMARY KEY CLUSTERED
(
[User] ASC,
[ID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
CREATE TABLE [dbo].[PriceTest](
[User] [int] NOT NULL,
[ID] [int] NOT NULL,
[Price] [money] NOT NULL,
CONSTRAINT [PK__PriceTest__696AB7F5] PRIMARY KEY CLUSTERED
(
[User] ASC,
[ID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
The sample data is posted in the first topic post.
Sample data is not in a format that can used readily to update the tables. Also, need you to provide some updates to the sample data and the expected results based on the sample data and sample updates.
April 17, 2012 at 10:14 am
Rank1_nj (4/17/2012)
Thanks for the response, but I am trying to reference dbo.MainMaster and update dbo.PriceTest with the values from dbo.MainMaster if the user is trying to insert values from the application that already exist in dbo.PriceTest
Looks like you can replace the update above with this
UPDATE p SET Price = m.Price
FROM PriceTest p
INNER JOIN MainMaster m ON p.ID = m.ID AND p.User = m.User
WHERE m.User = @User AND m.ID = @ID
______________________________________________________________________________
How I want a drink, alcoholic of course, after the heavy lectures involving quantum mechanics.
April 17, 2012 at 12:26 pm
Looks like you can replace the update above with this
UPDATE p SET Price = m.Price
FROM PriceTest p
INNER JOIN MainMaster m ON p.ID = m.ID AND p.User = m.User
WHERE m.User = @User AND m.ID = @ID
Thanks, yeah that works. Thanks again!
April 18, 2012 at 3:39 am
Mmmm.
Now this may sound like a stupid question to some of the "guru's" on this forum. Why is there a need for a master table and a separate identical table for use by an application. Doesn't this just at least double the disk space required, add 'expense' to the system and complicate the whole structure?
Thanks,
April 18, 2012 at 6:55 am
Fear Naught (4/18/2012)
Mmmm.Now this may sound like a stupid question to some of the "guru's" on this forum. Why is there a need for a master table and a separate identical table for use by an application. Doesn't this just at least double the disk space required, add 'expense' to the system and complicate the whole structure?
Thanks,
Disk space is usually the least of your concerns when designing a database. Not knowing the whole design, we won't know if this system is optimal or not. Rank1_nj may be able to answer why the second table is used.
______________________________________________________________________________
How I want a drink, alcoholic of course, after the heavy lectures involving quantum mechanics.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply