SQL to SQLite

Question

Post reply

SQL to SQLite

gideon.e

SSC Veteran

Points: 273

December 2, 2019 at 10:42 am

Go to Answer

#3703371

Can anyone point me to a SQL to SQlite converter? I have tried RebaseData, but it failed.

The query I want to convert is :

UPDATE whB
SET yearclass = top_payclasses.payclass
FROM dbo.wbridge_history whB
INNER JOIN (
SELECT grower, block, section, oesjaar, payclass,
--COUNT(*) AS payclass_count, SUM(weight) AS payclass_tie_breaker,
ROW_NUMBER() OVER(PARTITION BY grower, block, section, oesjaar
ORDER BY COUNT(*) DESC, SUM(net) DESC) AS row_num
FROM dbo.wbridge_history
GROUP BY grower, block, section, oesjaar, payclass
) AS top_payclasses ON
top_payclasses.row_num = 1 AND
top_payclasses.grower = whB.grower AND
top_payclasses.block = whB.block AND
top_payclasses.section = whB.section AND
top_payclasses.oesjaar = whB.oesjaar

Regards

Ken McKelvey

SSCoach

Points: 18842

December 2, 2019 at 8:37 pm

Answer

#3703595

I don't know much about sqlite but I doubt if joins in updates are allowed.

You could try something simple like:

UPDATE wbridge_history
SET yearclass =
(
SELECT D.payclass
FROM
(
SELECT grower, block, section, oesjaar, payclass,
ROW_NUMBER() OVER(PARTITION BY grower, block, section, oesjaar
ORDER BY COUNT(*) DESC, SUM(net) DESC) AS row_num
FROM wbridge_history
GROUP BY grower, block, section, oesjaar, payclass
) D
WHERE D.grower = wbridge_history.grower
AND D.block = wbridge_history.block
AND D.section = wbridge_history.section
AND D.oesjaar = wbridge_history.oesjaar
AND D.row_num = 1
);

This reply was modified 4 years, 11 months ago by Ken McKelvey.

Viewing 13 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply

Thom A SSC Guru Points: 99130 More actions · Answer 1

Honestly, I don't really trust converters. You're far better off doing this yourself. Yes, it might seem like a longer task, but converters make mistakes, can't replicate certain behaviour, or functionality isn't quite the same between different RDBMS. As a result if you use a converter, you'll likely be spending weeks (months) troubleshooting errors, and unexpected/undesired behaviour for code you haven't written or possible understand.

Do the work yourself, and you don't have that problem, and things will likely be working as intended far faster.

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 2

You do understand that you're making a major paradigm shift in code, right? SQL <> SQL between different RDBMS engines.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

gideon.e SSC Veteran Points: 273 More actions · Answer 3

Thanx Tom. You are right,but I just cannot figure out how to update an SQlite table without using inner join. I understand SQL, but SQLite has me stumped. I want to update a column from the most used text in a column based on conditions in other columns.

gideon.e SSC Veteran Points: 273 More actions · Answer 4

Thank you Jeff. Yes I am painfully aware that the code is different. It took me a very long time to figure out the SQL query and am really struggling to change it to sqlite.

Phil Parkin SSC Guru Points: 246960 More actions · Answer 5

gideon.e wrote:

Thank you Jeff. Yes I am painfully aware that the code is different. It took me a very long time to figure out the SQL query and am really struggling to change it to sqlite.

Have you tried posting in a SQLITE forum? They should know the required syntax better than we do here.

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

gideon.e SSC Veteran Points: 273 More actions · Answer 6

Hi Phil - thank you. Can you suggest a forum? I have tried https://sqlite.org/support.html, just cannot figure out how to ask a question.

This reply was modified 4 years, 11 months ago by gideon.e.

Phil Parkin SSC Guru Points: 246960 More actions · Answer 7

gideon.e wrote:

Hi Phil - thank you. Can you suggest a forum? I have tried https://sqlite.org/support.html, just cannot figure out how to ask a question.

No. I'm a SQL Server guy. That's why I'm in this forum 🙂

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

gideon.e SSC Veteran Points: 273 More actions · Answer 8

gideon.e

SSC Veteran

Points: 273

December 2, 2019 at 6:21 pm

#3703560

Thank you anyway Phil.

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 9

gideon.e wrote:

Thank you Jeff. Yes I am painfully aware that the code is different. It took me a very long time to figure out the SQL query and am really struggling to change it to sqlite.

I do feel your pain there. Imagine how I felt when they forced me to use Oracle for 3 years. Just like the problem with not being able to do joined Updates (and you can if you write the join as a correlated subquery) and a wad of other stuff. My way around the UPDATE problem was to use MERGE instead of UPDATEs because you CAN do a joined update that way. It's almost as easy as writing a joined update in SQL Server.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

gideon.e SSC Veteran Points: 273 More actions · Answer 10

Thank you very much Ken. Works perfectly in SQL, but in Sqlite it complains :

Error while executing SQL query on database 'weegbrugGeskiedenis': near "(": syntax error

The squigly line starts at (PARTITION ....

gideon.e SSC Veteran Points: 273 More actions · Answer 11

Hi all, thank you for your help. The problem seems to be SQL studio. DB Browser (SQLite) as well as a VB.net application executes the query fine.