SQLTeam.com | Weblogs | Forums

SQL logic needed

Hi guys,

I have a simple SQL Server table as follows;

CREATE TABLE [dbo].[Products](
	[Id] [int] IDENTITY(1,1) NOT NULL,
	[gameCode] [nvarchar](max) NULL,
	[price] [nvarchar](20) NULL,
	[currency] [varchar](25) NULL,
 CONSTRAINT [PK_dbo.ProductCodes] PRIMARY KEY CLUSTERED 
(
	[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

And some sample data would be

INSERT INTO [dbo].[ProductCodes] ([gameCode],[price],[currency]) VALUES ('20','2.99','USD')
INSERT INTO [dbo].[ProductCodes] ([gameCode],[price],[currency]) VALUES ('20','5','EUR')
INSERT INTO [dbo].[ProductCodes] ([gameCode],[price],[currency]) VALUES ('27','10','TUR')
INSERT INTO [dbo].[ProductCodes] ([gameCode],[price],[currency]) VALUES ('41','10.50','USD')
INSERT INTO [dbo].[ProductCodes] ([gameCode],[price],[currency]) VALUES ('1550','20','USD')
INSERT INTO [dbo].[ProductCodes] ([gameCode],[price],[currency]) VALUES ('1700','50','EUR')
INSERT INTO [dbo].[ProductCodes] ([gameCode],[price],[currency]) VALUES ('2000','99.99','USD')
INSERT INTO [dbo].[ProductCodes] ([gameCode],[price],[currency]) VALUES ('2000','125','TUR')
INSERT INTO [dbo].[ProductCodes] ([gameCode],[price],[currency]) VALUES ('2022','500','USD')

What I am trying to do is get price and currency data from a web service based on gamecodes. But the problem is there are the same gamecodes (like 20 and 2000) so I need to differentiate with the price. So I wonder if I can use some query like this to update data that comes from the web service.

sample query (in my program I will call this query inside of a loop)

Update ProductCodes P
SET price = @priceFromService, currency = @currencyFromService 
FROM 
    productCodes o 
  JOIN 
    P ON P.id = o.id
WHERE 
    P.gamecode= @gamecodeFromService and P.price = @priceFromService

Any comments would be great, thank you.

not alot of info here. You created a table called Products, then insert into a table called ProductCodes. Looks like you'll need to at least add Currency code to your join, but it'd be more helpful to add ddl for both objects and sample data

the table name will be ProductCodes, I typed it wrong.

INSERT INTO dbo.ProductCodes (gameCode, price, currency)
SELECT @gamecodeFromService, @priceFromService, @currencyFromService
WHERE NOT EXISTS
(
	SELECT 1
	FROM dbo.ProductCodes WITH (UPDLOCK, SERIALIZABLE)
	WHERE gameCode = @gamecodeFromService
		AND currency = @currencyFromService
);
IF @@ROWCOUNT = 0
	UPDATE dbo.ProductCodes
	SET price = @priceFromService
	WHERE gameCode = @gamecodeFromService
		AND currency = @currencyFromService;

that's a long way to go for that. Is GameCode and Currency the natural key for this table? If so, a simple merge statement would work and it's easier to read

merge into ProductCodes as target using (SELECT @gamecodeFromService as gameCode, @priceFromService as price, @currencyFromService as currency) as source on target.gameCode = source.gameCode and target.Currency = source.Currency when matched then update.... when not matched then insert.....