Styjun

Phrase for the opposite of "foolproof"

What is causing the white spot to appear in some of my pictures

Pre-plastic human skin alternative

How to prevent z-fighting in OpenSCAD?

What is the philosophical significance of speech acts/implicature?

Dynamic SOQL query relationship with field visibility for Users

Elements other than carbon that can form many different compounds by bonding to themselves?

I preordered a game on my Xbox while on the home screen of my friend's account. Which of us owns the game?

Re-entry to Germany after vacation using blue card

How to fry ground beef so it is well-browned

What happens to Mjolnir (Thor's hammer) at the end of Endgame?

How could Tony Stark make this in Endgame?

What does the integral of a function times a function of a random variable represent, conceptually?

How to write a column outside the braces in a matrix?

"Whatever a Russian does, they end up making the Kalashnikov gun"? Are there any similar proverbs in English?

Can I criticise the more senior developers around me for not writing clean code?

Is there really no use for MD5 anymore?

Can someone publish a story that happened to you?

How to denote matrix elements succinctly?

Can we say “you can pay when the order gets ready”?

How to display Aura JS Errors Lightning Out

Classification of surfaces

555 timer FM transmitter

What happened to Captain America in Endgame?

H2 Database duplicates removal

H2 Embedded Mode - DELETE command giving COLUMN NOT FOUND errorWSO2 BAM Hive script error: Database may be already in useCannot insert minus sign into varchar column with check constraint (column <> '')Error while inserting in sql2oHow to insert if not exists with selecting from same table?COUNT with subquery fail on H2 database with “Duplicate column name”Fastest way to delete values witch no match in a collectionSpring JPA Repository is unable to create mapping tables of my entitiesH2 database Merge into based on one columnEclipseLink does not see created tables in H2 DB

.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty height:90px;width:728px;box-sizing:border-box;

1

I wish to operate on duplicate rows in a H2 database table (DELETE would be a chance, SETting new value another).

Let's prepare some sample data:

create table receipt( id int primary key, receipt_number varchar(52), shop_id int);
insert into receipt(id, receipt_number, shop_id) values(1,'A',1);
insert into receipt(id, receipt_number, shop_id) values(2,'A',1);
insert into receipt(id, receipt_number, shop_id) values(3,'B',1);
insert into receipt(id, receipt_number, shop_id) values(4,'A',2);
select * from receipt;
select receipt_number, shop_id, count(*) count from receipt group by (receipt_number, shop_id);

wich leads us to:

I thought that this statement would be enought:

merge into receipt as t1
using ( select receipt_number, shop_id, count(*) count from receipt group by (receipt_number, shop_id) ) as t2 on t1.receipt_number = t2.receipt_number
when matched and count > 1 then delete;

but an exception is raised:

Syntax error in SQL statement "MERGE INTO RECEIPT AS T1 
 USING ( SELECT RECEIPT_NUMBER, SHOP_ID, COUNT(*) COUNT FROM RECEIPT GROUP BY (RECEIPT_NUMBER, SHOP_ID) ) AS T2 ON T1[*].RECEIPT_NUMBER = T2.RECEIPT_NUMBER 
 WHEN MATCHED AND COUNT > 1 THEN DELETE "; expected "("; SQL statement:
merge into receipt as t1 
 using ( select receipt_number, shop_id, count(*) count from receipt group by (receipt_number, shop_id) ) as t2 on t1.receipt_number = t2.receipt_number 
 when matched and count > 1 then delete [42001-197] 42001/42001

You can easily test my code running

docker run -d -p 1521:1521 -p 8082:81 oscarfonts/h2 

the browse to http://localhost:8082/ login and copy&paste code.

h2

share|improve this question

asked Mar 22 at 17:22

lrkwzlrkwz

4,01722644

add a comment | 

1

I wish to operate on duplicate rows in a H2 database table (DELETE would be a chance, SETting new value another).

Let's prepare some sample data:

create table receipt( id int primary key, receipt_number varchar(52), shop_id int);
insert into receipt(id, receipt_number, shop_id) values(1,'A',1);
insert into receipt(id, receipt_number, shop_id) values(2,'A',1);
insert into receipt(id, receipt_number, shop_id) values(3,'B',1);
insert into receipt(id, receipt_number, shop_id) values(4,'A',2);
select * from receipt;
select receipt_number, shop_id, count(*) count from receipt group by (receipt_number, shop_id);

wich leads us to:

I thought that this statement would be enought:

merge into receipt as t1
using ( select receipt_number, shop_id, count(*) count from receipt group by (receipt_number, shop_id) ) as t2 on t1.receipt_number = t2.receipt_number
when matched and count > 1 then delete;

but an exception is raised:

Syntax error in SQL statement "MERGE INTO RECEIPT AS T1 
 USING ( SELECT RECEIPT_NUMBER, SHOP_ID, COUNT(*) COUNT FROM RECEIPT GROUP BY (RECEIPT_NUMBER, SHOP_ID) ) AS T2 ON T1[*].RECEIPT_NUMBER = T2.RECEIPT_NUMBER 
 WHEN MATCHED AND COUNT > 1 THEN DELETE "; expected "("; SQL statement:
merge into receipt as t1 
 using ( select receipt_number, shop_id, count(*) count from receipt group by (receipt_number, shop_id) ) as t2 on t1.receipt_number = t2.receipt_number 
 when matched and count > 1 then delete [42001-197] 42001/42001

You can easily test my code running

docker run -d -p 1521:1521 -p 8082:81 oscarfonts/h2 

the browse to http://localhost:8082/ login and copy&paste code.

h2

share|improve this question

asked Mar 22 at 17:22

lrkwzlrkwz

4,01722644

add a comment | 

I wish to operate on duplicate rows in a H2 database table (DELETE would be a chance, SETting new value another).

Let's prepare some sample data:

create table receipt( id int primary key, receipt_number varchar(52), shop_id int);
insert into receipt(id, receipt_number, shop_id) values(1,'A',1);
insert into receipt(id, receipt_number, shop_id) values(2,'A',1);
insert into receipt(id, receipt_number, shop_id) values(3,'B',1);
insert into receipt(id, receipt_number, shop_id) values(4,'A',2);
select * from receipt;
select receipt_number, shop_id, count(*) count from receipt group by (receipt_number, shop_id);

wich leads us to:

I thought that this statement would be enought:

merge into receipt as t1
using ( select receipt_number, shop_id, count(*) count from receipt group by (receipt_number, shop_id) ) as t2 on t1.receipt_number = t2.receipt_number
when matched and count > 1 then delete;

but an exception is raised:

Syntax error in SQL statement "MERGE INTO RECEIPT AS T1 
 USING ( SELECT RECEIPT_NUMBER, SHOP_ID, COUNT(*) COUNT FROM RECEIPT GROUP BY (RECEIPT_NUMBER, SHOP_ID) ) AS T2 ON T1[*].RECEIPT_NUMBER = T2.RECEIPT_NUMBER 
 WHEN MATCHED AND COUNT > 1 THEN DELETE "; expected "("; SQL statement:
merge into receipt as t1 
 using ( select receipt_number, shop_id, count(*) count from receipt group by (receipt_number, shop_id) ) as t2 on t1.receipt_number = t2.receipt_number 
 when matched and count > 1 then delete [42001-197] 42001/42001

You can easily test my code running

docker run -d -p 1521:1521 -p 8082:81 oscarfonts/h2 

the browse to http://localhost:8082/ login and copy&paste code.

h2

share|improve this question

asked Mar 22 at 17:22

lrkwzlrkwz

4,01722644

create table receipt( id int primary key, receipt_number varchar(52), shop_id int);
insert into receipt(id, receipt_number, shop_id) values(1,'A',1);
insert into receipt(id, receipt_number, shop_id) values(2,'A',1);
insert into receipt(id, receipt_number, shop_id) values(3,'B',1);
insert into receipt(id, receipt_number, shop_id) values(4,'A',2);
select * from receipt;
select receipt_number, shop_id, count(*) count from receipt group by (receipt_number, shop_id);

merge into receipt as t1
using ( select receipt_number, shop_id, count(*) count from receipt group by (receipt_number, shop_id) ) as t2 on t1.receipt_number = t2.receipt_number
when matched and count > 1 then delete;

Syntax error in SQL statement "MERGE INTO RECEIPT AS T1 
 USING ( SELECT RECEIPT_NUMBER, SHOP_ID, COUNT(*) COUNT FROM RECEIPT GROUP BY (RECEIPT_NUMBER, SHOP_ID) ) AS T2 ON T1[*].RECEIPT_NUMBER = T2.RECEIPT_NUMBER 
 WHEN MATCHED AND COUNT > 1 THEN DELETE "; expected "("; SQL statement:
merge into receipt as t1 
 using ( select receipt_number, shop_id, count(*) count from receipt group by (receipt_number, shop_id) ) as t2 on t1.receipt_number = t2.receipt_number 
 when matched and count > 1 then delete [42001-197] 42001/42001

docker run -d -p 1521:1521 -p 8082:81 oscarfonts/h2 

share|improve this question

asked Mar 22 at 17:22

lrkwzlrkwz

4,01722644

share|improve this question

asked Mar 22 at 17:22

lrkwzlrkwz

4,01722644

asked Mar 22 at 17:22

lrkwzlrkwz

4,01722644

asked Mar 22 at 17:22

lrkwzlrkwz

4,01722644

1 Answer
1

active

oldest

votes

0

I think I solved it with

delete receipt 
where id > (select min(id) from receipt t2 where receipt.receipt_number = t2.receipt_number and receipt.shop_id = t2.shop_id )

guess I can also use it for update.

share|improve this answer

answered Mar 22 at 19:40

lrkwzlrkwz

4,01722644

add a comment | 

Your Answer

StackExchange.ifUsing("editor", function ()
StackExchange.using("externalEditor", function ()
StackExchange.using("snippets", function ()
StackExchange.snippets.init();
);
);
, "code-snippets");

StackExchange.ready(function()
var channelOptions =
tags: "".split(" "),
id: "1"
;
initTagRenderer("".split(" "), "".split(" "), channelOptions);

StackExchange.using("externalEditor", function()
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled)
StackExchange.using("snippets", function()
createEditor();
);

else
createEditor();

);

function createEditor()
StackExchange.prepareEditor(
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader:
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
,
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
);



);

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);

Sign up using Google

Sign up using Facebook

Sign up using Email and Password

Post as a guest

Name

Email

Required, but never shown

StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f55304827%2fh2-database-duplicates-removal%23new-answer', 'question_page');

);

Post as a guest

Name

Email

Required, but never shown

0

I think I solved it with

delete receipt 
where id > (select min(id) from receipt t2 where receipt.receipt_number = t2.receipt_number and receipt.shop_id = t2.shop_id )

guess I can also use it for update.

share|improve this answer

answered Mar 22 at 19:40

lrkwzlrkwz

4,01722644

add a comment | 

0

I think I solved it with

delete receipt 
where id > (select min(id) from receipt t2 where receipt.receipt_number = t2.receipt_number and receipt.shop_id = t2.shop_id )

guess I can also use it for update.

share|improve this answer

answered Mar 22 at 19:40

lrkwzlrkwz

4,01722644

add a comment | 

I think I solved it with

delete receipt 
where id > (select min(id) from receipt t2 where receipt.receipt_number = t2.receipt_number and receipt.shop_id = t2.shop_id )

guess I can also use it for update.

share|improve this answer

answered Mar 22 at 19:40

lrkwzlrkwz

4,01722644

delete receipt 
where id > (select min(id) from receipt t2 where receipt.receipt_number = t2.receipt_number and receipt.shop_id = t2.shop_id )

share|improve this answer

answered Mar 22 at 19:40

lrkwzlrkwz

4,01722644

answered Mar 22 at 19:40

lrkwzlrkwz

4,01722644

answered Mar 22 at 19:40

lrkwzlrkwz

4,01722644