SQL logic needed

Transact-SQL
1

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.

2

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

3

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

4 
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;
1 Like
5

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.....

1 Like