Styjun

Linux ext4 restore file and directory access rights after bad backup/restore

"Je suis petite, moi?", purpose of the "moi"?

Formating slide

Do Indians need sepearte Hong Kong visa if we already have Chinese visa

Applying for jobs with an obvious scar

Did I pick the wrong bike?

Authorship dispute on a paper that came out of a final report of a course?

Which family is it?

How to tell readers that I know my story is factually incorrect?

Do pedestrians imitate automotive traffic?

Project Euler # 25 The 1000 digit Fibonacci index

Improving an O(N^2) function (all entities iterating over all other entities)

What is a Kravchuk transform and how is it related to Fourier transforms?

Align the contents of a numerical matrix when you have minus signs

What's a German word for »Sandbagger«?

Transistor power dissipation rating

Was demon possession only a New Testament phenomenon?

Inscriptio Labyrinthica

Why is carrying a heavy object more taxing on the body than pushing the same object on wheels?

Why is this guy handcuffed censored?

The most secure way to handle someone forgetting to verify their account?

What is the intuition for higher homotopy groups not vanishing?

Deleting a point in METAFONT

Why would word of Princess Leia's capture generate sympathy for the Rebellion in the Senate?

Remove all rows corresponding a specific ID if the value of a column match

How using semicolon in a stringAdd a column with a default value to an existing table in SQL ServerFetch the row which has the Max value for a columnHow can I SELECT rows with MAX(Column value), DISTINCT by another column in SQL?How do I write this SQL query?How to select rows with no matching entry in another table?Find all tables containing column with specified name - MS SQL ServerSQL select only rows with max value on a columnHow to write a SQL command to sort multiple rowsSQL - Select rows where all values in a column are the sameDistinct values one column with other columns from two tables

.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty margin-bottom:0;

0

1

I am trying to delete all lines containing an ID if the value of a column in one of the rows matches the value sought

I have the following tables :

table1:

| ID | | offer |
-------------------------
| 001 | | 'SE;SL' |
| 001 | | 'WX' |
| 001 | | 'PW' |

| 002 | | 'WS' |
| 002 | | 'EL' |

| 003 | | 'SE' |

| 004 | | 'ZS' |
| 004 | |'PW;SE;ODL'|

SELECT ID,
 offer
FROM table1
GROUP BY 
 ID,
 offer,
HAVING sum(case when offer LIKE '%SE%' then 1 else 0 end) = 0

For the moment, I am able to delete the line as follows:

| ID | | offer |
-------------------------
| 001 | | 'WX' |
| 001 | | 'PW' |

| 002 | | 'WS' |
| 002 | | 'EL' |

| 004 | | 'ZS' |

Expected result

| ID | | offer |
-------------------------
| 002 | | 'WS' |
| 002 | | 'EL' |

As you can see, I try to isolate all the IDs for which it appears at least once "SE" in one of the rows of the "offer" column.

I have tried many things, done several searches without success, if someone can help me understand!

Thanks so muchh

sql postgresql

share|improve this question

asked Mar 26 at 11:43

pondiapondia

844 bronze badges

• 
  
  
  

  
  1
  

  
  
  
  
  
  

  
  
  Fix your data model! Do not store lists of strings as a string in a column.
  
  – Gordon Linoff
  Mar 26 at 11:45
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Thanks for your comment. I'm totaly agree with that. unfortunatly it's not my data model.. and I tell them (to the company I work on, (im still student)) that's not a good practice but they don't care ...
  
  – pondia
  Mar 26 at 12:58
  

  
  
  
  

add a comment | 

0

1

I am trying to delete all lines containing an ID if the value of a column in one of the rows matches the value sought

I have the following tables :

table1:

| ID | | offer |
-------------------------
| 001 | | 'SE;SL' |
| 001 | | 'WX' |
| 001 | | 'PW' |

| 002 | | 'WS' |
| 002 | | 'EL' |

| 003 | | 'SE' |

| 004 | | 'ZS' |
| 004 | |'PW;SE;ODL'|

SELECT ID,
 offer
FROM table1
GROUP BY 
 ID,
 offer,
HAVING sum(case when offer LIKE '%SE%' then 1 else 0 end) = 0

For the moment, I am able to delete the line as follows:

| ID | | offer |
-------------------------
| 001 | | 'WX' |
| 001 | | 'PW' |

| 002 | | 'WS' |
| 002 | | 'EL' |

| 004 | | 'ZS' |

Expected result

| ID | | offer |
-------------------------
| 002 | | 'WS' |
| 002 | | 'EL' |

As you can see, I try to isolate all the IDs for which it appears at least once "SE" in one of the rows of the "offer" column.

I have tried many things, done several searches without success, if someone can help me understand!

Thanks so muchh

sql postgresql

share|improve this question

asked Mar 26 at 11:43

pondiapondia

844 bronze badges

• 
  
  
  

  
  1
  

  
  
  
  
  
  

  
  
  Fix your data model! Do not store lists of strings as a string in a column.
  
  – Gordon Linoff
  Mar 26 at 11:45
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Thanks for your comment. I'm totaly agree with that. unfortunatly it's not my data model.. and I tell them (to the company I work on, (im still student)) that's not a good practice but they don't care ...
  
  – pondia
  Mar 26 at 12:58
  

  
  
  
  

add a comment | 

I am trying to delete all lines containing an ID if the value of a column in one of the rows matches the value sought

I have the following tables :

table1:

| ID | | offer |
-------------------------
| 001 | | 'SE;SL' |
| 001 | | 'WX' |
| 001 | | 'PW' |

| 002 | | 'WS' |
| 002 | | 'EL' |

| 003 | | 'SE' |

| 004 | | 'ZS' |
| 004 | |'PW;SE;ODL'|

SELECT ID,
 offer
FROM table1
GROUP BY 
 ID,
 offer,
HAVING sum(case when offer LIKE '%SE%' then 1 else 0 end) = 0

For the moment, I am able to delete the line as follows:

| ID | | offer |
-------------------------
| 001 | | 'WX' |
| 001 | | 'PW' |

| 002 | | 'WS' |
| 002 | | 'EL' |

| 004 | | 'ZS' |

Expected result

| ID | | offer |
-------------------------
| 002 | | 'WS' |
| 002 | | 'EL' |

As you can see, I try to isolate all the IDs for which it appears at least once "SE" in one of the rows of the "offer" column.

I have tried many things, done several searches without success, if someone can help me understand!

Thanks so muchh

sql postgresql

share|improve this question

asked Mar 26 at 11:43

pondiapondia

844 bronze badges

| ID | | offer |
-------------------------
| 001 | | 'SE;SL' |
| 001 | | 'WX' |
| 001 | | 'PW' |

| 002 | | 'WS' |
| 002 | | 'EL' |

| 003 | | 'SE' |

| 004 | | 'ZS' |
| 004 | |'PW;SE;ODL'|

SELECT ID,
 offer
FROM table1
GROUP BY 
 ID,
 offer,
HAVING sum(case when offer LIKE '%SE%' then 1 else 0 end) = 0

| ID | | offer |
-------------------------
| 001 | | 'WX' |
| 001 | | 'PW' |

| 002 | | 'WS' |
| 002 | | 'EL' |

| 004 | | 'ZS' |

| ID | | offer |
-------------------------
| 002 | | 'WS' |
| 002 | | 'EL' |

share|improve this question

asked Mar 26 at 11:43

pondiapondia

844 bronze badges

share|improve this question

asked Mar 26 at 11:43

pondiapondia

844 bronze badges

asked Mar 26 at 11:43

pondiapondia

844 bronze badges

asked Mar 26 at 11:43

pondiapondia

844 bronze badges

2 Answers
2

active

oldest

votes

1

use not exists

SELECT ID,
 offer
FROM table1 t1
where not exists ( select 1 from table1 t2 where t1.id=t2.id
 and t2.offer like '%SE%')

share|improve this answer

answered Mar 26 at 11:44

Zaynul Abadin TuhinZaynul Abadin Tuhin

22.2k33 gold badges1414 silver badges3939 bronze badges

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  it will work only if the offers are 2 characters length. 'SEL;WS' won't give the expected result
  
  – Cid
  Mar 26 at 12:54
  

  
  
  
  

add a comment | 

1

With NOT EXISTS but you must check also the case the value you search is not part of another value.

So concatenate both the offer column and the value with ; at the start and at the end:

select id, offer
from table1 t
where not exists ( 
 select 1 from table1 
 where 
 id=t.id
 and 
 ';' || offer || ';' like '%;SE;%'
)

Edit

Replace the last line with:

chr(59) || offer || chr(59) like '%' || chr(59) || 'SE' || chr(59) || '%'

share|improve this answer

edited Mar 26 at 13:32

answered Mar 26 at 11:54

forpasforpas

34.6k55 gold badges1111 silver badges3131 bronze badges

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  thanks for you answer. I forget to specify that the tool I'm using does not support ';'. Do you thinks we can do this in an other way ? Regards,
  
  – pondia
  Mar 26 at 13:01
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  What do you mean does not support ;. It's just the delimeter char in the values you want to check.
  
  – forpas
  Mar 26 at 13:05
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  When I use semicolon i get this error : ⛔️An error occurred while checking the query syntax. Errors: ';' is a reserved word and may not appear in your query. I'm working on Salesforce Marketing Cloud. And the tool who check my syntax don't like ;
  
  – pondia
  Mar 26 at 13:13
  
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Did you copy and paste the code as it is? Because when you use this ';' (inside single quotes) there is no way to get a syntax error. Isn't this Postgersql?
  
  – forpas
  Mar 26 at 13:20
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Check my edited answer.
  
  – forpas
  Mar 26 at 13:24
  

  
  
  
  

 | 
show 2 more comments

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%2f55356322%2fremove-all-rows-corresponding-a-specific-id-if-the-value-of-a-column-match%23new-answer', 'question_page');

);

Post as a guest

Name

Email

Required, but never shown

1

use not exists

SELECT ID,
 offer
FROM table1 t1
where not exists ( select 1 from table1 t2 where t1.id=t2.id
 and t2.offer like '%SE%')

share|improve this answer

answered Mar 26 at 11:44

Zaynul Abadin TuhinZaynul Abadin Tuhin

22.2k33 gold badges1414 silver badges3939 bronze badges

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  it will work only if the offers are 2 characters length. 'SEL;WS' won't give the expected result
  
  – Cid
  Mar 26 at 12:54
  

  
  
  
  

add a comment | 

1

use not exists

SELECT ID,
 offer
FROM table1 t1
where not exists ( select 1 from table1 t2 where t1.id=t2.id
 and t2.offer like '%SE%')

share|improve this answer

answered Mar 26 at 11:44

Zaynul Abadin TuhinZaynul Abadin Tuhin

22.2k33 gold badges1414 silver badges3939 bronze badges

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  it will work only if the offers are 2 characters length. 'SEL;WS' won't give the expected result
  
  – Cid
  Mar 26 at 12:54
  

  
  
  
  

add a comment | 

use not exists

SELECT ID,
 offer
FROM table1 t1
where not exists ( select 1 from table1 t2 where t1.id=t2.id
 and t2.offer like '%SE%')

share|improve this answer

answered Mar 26 at 11:44

Zaynul Abadin TuhinZaynul Abadin Tuhin

22.2k33 gold badges1414 silver badges3939 bronze badges

SELECT ID,
 offer
FROM table1 t1
where not exists ( select 1 from table1 t2 where t1.id=t2.id
 and t2.offer like '%SE%')

share|improve this answer

answered Mar 26 at 11:44

Zaynul Abadin TuhinZaynul Abadin Tuhin

22.2k33 gold badges1414 silver badges3939 bronze badges

answered Mar 26 at 11:44

Zaynul Abadin TuhinZaynul Abadin Tuhin

22.2k33 gold badges1414 silver badges3939 bronze badges

answered Mar 26 at 11:44

Zaynul Abadin TuhinZaynul Abadin Tuhin

22.2k33 gold badges1414 silver badges3939 bronze badges

1

With NOT EXISTS but you must check also the case the value you search is not part of another value.

So concatenate both the offer column and the value with ; at the start and at the end:

select id, offer
from table1 t
where not exists ( 
 select 1 from table1 
 where 
 id=t.id
 and 
 ';' || offer || ';' like '%;SE;%'
)

Edit

Replace the last line with:

chr(59) || offer || chr(59) like '%' || chr(59) || 'SE' || chr(59) || '%'

share|improve this answer

edited Mar 26 at 13:32

answered Mar 26 at 11:54

forpasforpas

34.6k55 gold badges1111 silver badges3131 bronze badges

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  thanks for you answer. I forget to specify that the tool I'm using does not support ';'. Do you thinks we can do this in an other way ? Regards,
  
  – pondia
  Mar 26 at 13:01
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  What do you mean does not support ;. It's just the delimeter char in the values you want to check.
  
  – forpas
  Mar 26 at 13:05
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  When I use semicolon i get this error : ⛔️An error occurred while checking the query syntax. Errors: ';' is a reserved word and may not appear in your query. I'm working on Salesforce Marketing Cloud. And the tool who check my syntax don't like ;
  
  – pondia
  Mar 26 at 13:13
  
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Did you copy and paste the code as it is? Because when you use this ';' (inside single quotes) there is no way to get a syntax error. Isn't this Postgersql?
  
  – forpas
  Mar 26 at 13:20
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Check my edited answer.
  
  – forpas
  Mar 26 at 13:24
  

  
  
  
  

 | 
show 2 more comments

1

With NOT EXISTS but you must check also the case the value you search is not part of another value.

So concatenate both the offer column and the value with ; at the start and at the end:

select id, offer
from table1 t
where not exists ( 
 select 1 from table1 
 where 
 id=t.id
 and 
 ';' || offer || ';' like '%;SE;%'
)

Edit

Replace the last line with:

chr(59) || offer || chr(59) like '%' || chr(59) || 'SE' || chr(59) || '%'

share|improve this answer

edited Mar 26 at 13:32

answered Mar 26 at 11:54

forpasforpas

34.6k55 gold badges1111 silver badges3131 bronze badges

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  thanks for you answer. I forget to specify that the tool I'm using does not support ';'. Do you thinks we can do this in an other way ? Regards,
  
  – pondia
  Mar 26 at 13:01
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  What do you mean does not support ;. It's just the delimeter char in the values you want to check.
  
  – forpas
  Mar 26 at 13:05
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  When I use semicolon i get this error : ⛔️An error occurred while checking the query syntax. Errors: ';' is a reserved word and may not appear in your query. I'm working on Salesforce Marketing Cloud. And the tool who check my syntax don't like ;
  
  – pondia
  Mar 26 at 13:13
  
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Did you copy and paste the code as it is? Because when you use this ';' (inside single quotes) there is no way to get a syntax error. Isn't this Postgersql?
  
  – forpas
  Mar 26 at 13:20
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Check my edited answer.
  
  – forpas
  Mar 26 at 13:24
  

  
  
  
  

 | 
show 2 more comments

With NOT EXISTS but you must check also the case the value you search is not part of another value.

So concatenate both the offer column and the value with ; at the start and at the end:

select id, offer
from table1 t
where not exists ( 
 select 1 from table1 
 where 
 id=t.id
 and 
 ';' || offer || ';' like '%;SE;%'
)

Edit

Replace the last line with:

chr(59) || offer || chr(59) like '%' || chr(59) || 'SE' || chr(59) || '%'

share|improve this answer

edited Mar 26 at 13:32

answered Mar 26 at 11:54

forpasforpas

34.6k55 gold badges1111 silver badges3131 bronze badges

select id, offer
from table1 t
where not exists ( 
 select 1 from table1 
 where 
 id=t.id
 and 
 ';' || offer || ';' like '%;SE;%'
)

chr(59) || offer || chr(59) like '%' || chr(59) || 'SE' || chr(59) || '%'

share|improve this answer

edited Mar 26 at 13:32

answered Mar 26 at 11:54

forpasforpas

34.6k55 gold badges1111 silver badges3131 bronze badges

answered Mar 26 at 11:54

forpasforpas

34.6k55 gold badges1111 silver badges3131 bronze badges

answered Mar 26 at 11:54

forpasforpas

34.6k55 gold badges1111 silver badges3131 bronze badges