Styjun

Count the number of paths to n

Cooking Scrambled Eggs

What should come first—characters or plot?

50-move rule: only the last 50 or any consecutive 50?

What are the occurences of total war in the Native Americans?

Unlock your Lock

Why is proof-of-work required in Bitcoin?

Did anybody find out it was Anakin who blew up the command center?

How would a low-tech device be able to alert its user?

I don't have the theoretical background in my PhD topic. I can't justify getting the degree

Redacting URLs as an email-phishing preventative?

What is Spectral Subtraction for noise reduction?

using resizegather inside tabular: how to remove extra vertical space above and below the equation?

How to gently end involvement with an online community?

Joining lists with same elements

Duplicate instruments in unison in an orchestra

Can RMSE and MAE have the same value?

Why should a self-financing strategy be previsible?

Why is "-ber" the suffix of the last four months of the year?

How should i charge 3 lithium ion batteries?

What to look for in a spotting scope?

How to check whether a sublist exist in a huge database lists in a fast way?

How much does Commander Data weigh?

Was the Boeing 2707 design flawed?

SQL Update Based on Multi-Table Computed Values

Add a column with a default value to an existing table in SQL ServerSQL update from one Table to another based on a ID matchHow can I do an UPDATE statement with JOIN in SQL?How do I UPDATE from a SELECT in SQL Server?Finding duplicate values in a SQL tableInsert into a MySQL table or update if existsFind all tables containing column with specified name - MS SQL ServerSQL select only rows with max value on a columnConvert multiple SQL code with multiple subqueries into a single queryHow to insert into one column from two table's two different column?

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

1

I have a SQL statement that will be run daily against my db; logically, it will set a value on one table to null after a period of time has elapsed. However, determining whether that period has passed requires examining two other tables and then calculating the length of time that has passed based on the values contained therein.

My current SQL statement works for these purposes (see below) but as it relies on the use of an IN clause, there's the possibility it'll run afoul of the 1000 element limit that applies to IN clauses. I'd like to reformat this query away from relying on an IN so that it doesn't matter how many records this update is touching.

UPDATE table1 SET table1.field1 = null
WHERE table1.table1_id IN
(SELECT table1_id
 FROM (SELECT table1_id, (SYSDATE - MAX(table2.date_field)) days
 WHERE table1.field1 IS NOT null
 AND table1.table1_id = table2.table1_fk
 AND table2.table3_fk = table3.table3_id
 AND (table3.type = X OR table3.type = Y)
 GROUP BY table1_id) tmp_table
 WHERE days >= cutoff)

How can this be rewritten such that field1 of table1 is set appropriately to null without relying on the potentially fallible IN clause? This is an Oracle db, to whatever extent that effects the possible solutions.

sql oracle

share|improve this question

asked Mar 27 at 19:40

calibercaliber

822 bronze badges

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  What do you mean "run afoul of the 1000 element limit"? Does your inner query return expected results?
  
  – Pavel Smirnov
  Mar 27 at 19:55
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  As part of a different query, I recently found out oracle caps the number of elements in an IN clause to 1000, and throws an exception when that's exceeded. It seems unlikely, but if there were a large number of records that ticked over at the same time, the IN clause could possibly trip that limit? I'm not entirely sure if it applies to a sub-query vs a straight list of values, but it's something I'm trying to stay aware of now.
  
  – caliber
  Mar 27 at 20:56
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Oracle does not have such limits. It seems there was a problem with the query that threw an exception.
  
  – Pavel Smirnov
  Mar 27 at 21:00
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  @PavelSmirnov ORA-01795!!!
  
  – hbourchi
  Mar 27 at 21:45
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  @hbourchi, that is not subquery limits. It's the max size of simple values, like in ('value1', 'value2', 'value3'...'value1000')
  
  – Pavel Smirnov
  Mar 27 at 21:48
  

  
  
  
  

 | 
show 1 more comment

1

I have a SQL statement that will be run daily against my db; logically, it will set a value on one table to null after a period of time has elapsed. However, determining whether that period has passed requires examining two other tables and then calculating the length of time that has passed based on the values contained therein.

My current SQL statement works for these purposes (see below) but as it relies on the use of an IN clause, there's the possibility it'll run afoul of the 1000 element limit that applies to IN clauses. I'd like to reformat this query away from relying on an IN so that it doesn't matter how many records this update is touching.

UPDATE table1 SET table1.field1 = null
WHERE table1.table1_id IN
(SELECT table1_id
 FROM (SELECT table1_id, (SYSDATE - MAX(table2.date_field)) days
 WHERE table1.field1 IS NOT null
 AND table1.table1_id = table2.table1_fk
 AND table2.table3_fk = table3.table3_id
 AND (table3.type = X OR table3.type = Y)
 GROUP BY table1_id) tmp_table
 WHERE days >= cutoff)

How can this be rewritten such that field1 of table1 is set appropriately to null without relying on the potentially fallible IN clause? This is an Oracle db, to whatever extent that effects the possible solutions.

sql oracle

share|improve this question

asked Mar 27 at 19:40

calibercaliber

822 bronze badges

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  What do you mean "run afoul of the 1000 element limit"? Does your inner query return expected results?
  
  – Pavel Smirnov
  Mar 27 at 19:55
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  As part of a different query, I recently found out oracle caps the number of elements in an IN clause to 1000, and throws an exception when that's exceeded. It seems unlikely, but if there were a large number of records that ticked over at the same time, the IN clause could possibly trip that limit? I'm not entirely sure if it applies to a sub-query vs a straight list of values, but it's something I'm trying to stay aware of now.
  
  – caliber
  Mar 27 at 20:56
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Oracle does not have such limits. It seems there was a problem with the query that threw an exception.
  
  – Pavel Smirnov
  Mar 27 at 21:00
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  @PavelSmirnov ORA-01795!!!
  
  – hbourchi
  Mar 27 at 21:45
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  @hbourchi, that is not subquery limits. It's the max size of simple values, like in ('value1', 'value2', 'value3'...'value1000')
  
  – Pavel Smirnov
  Mar 27 at 21:48
  

  
  
  
  

 | 
show 1 more comment

I have a SQL statement that will be run daily against my db; logically, it will set a value on one table to null after a period of time has elapsed. However, determining whether that period has passed requires examining two other tables and then calculating the length of time that has passed based on the values contained therein.

My current SQL statement works for these purposes (see below) but as it relies on the use of an IN clause, there's the possibility it'll run afoul of the 1000 element limit that applies to IN clauses. I'd like to reformat this query away from relying on an IN so that it doesn't matter how many records this update is touching.

UPDATE table1 SET table1.field1 = null
WHERE table1.table1_id IN
(SELECT table1_id
 FROM (SELECT table1_id, (SYSDATE - MAX(table2.date_field)) days
 WHERE table1.field1 IS NOT null
 AND table1.table1_id = table2.table1_fk
 AND table2.table3_fk = table3.table3_id
 AND (table3.type = X OR table3.type = Y)
 GROUP BY table1_id) tmp_table
 WHERE days >= cutoff)

How can this be rewritten such that field1 of table1 is set appropriately to null without relying on the potentially fallible IN clause? This is an Oracle db, to whatever extent that effects the possible solutions.

sql oracle

share|improve this question

asked Mar 27 at 19:40

calibercaliber

822 bronze badges

UPDATE table1 SET table1.field1 = null
WHERE table1.table1_id IN
(SELECT table1_id
 FROM (SELECT table1_id, (SYSDATE - MAX(table2.date_field)) days
 WHERE table1.field1 IS NOT null
 AND table1.table1_id = table2.table1_fk
 AND table2.table3_fk = table3.table3_id
 AND (table3.type = X OR table3.type = Y)
 GROUP BY table1_id) tmp_table
 WHERE days >= cutoff)

share|improve this question

asked Mar 27 at 19:40

calibercaliber

822 bronze badges

share|improve this question

asked Mar 27 at 19:40

calibercaliber

822 bronze badges

asked Mar 27 at 19:40

calibercaliber

822 bronze badges

asked Mar 27 at 19:40

calibercaliber

822 bronze badges

1 Answer
1

active

oldest

votes

0

Not sure if I understand your question fully but another way to write your query is using EXISTS. something like this:

UPDATE table1
SET table1.field1 = null
WHERE exists 
(SELECT null
 FROM (SELECT table1_id, (SYSDATE - MAX(table2.date_field)) days
 WHERE table1.field1 IS NOT null
 AND table1.table1_id = table2.table1_fk
 AND table2.table3_fk = table3.table3_id
 AND (table3.type = X OR table3.type = Y)
 GROUP BY table1_id) tmp_table
WHERE days >= cutoff
and table1.table1_id = tmp_table.table1_id)

share|improve this answer

answered Mar 27 at 20:06

hbourchihbourchi

18477 bronze badges

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  The sub-sub query is missing a FROM statement, and I changed the SELECT null to SELECT table1.table1_id, but this seems to work! I figured it was going to be something simple, thanks for this.
  
  – caliber
  Mar 27 at 20:54
  

  
  
  
  

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%2f55385260%2fsql-update-based-on-multi-table-computed-values%23new-answer', 'question_page');

);

Post as a guest

Name

Email

Required, but never shown

0

Not sure if I understand your question fully but another way to write your query is using EXISTS. something like this:

UPDATE table1
SET table1.field1 = null
WHERE exists 
(SELECT null
 FROM (SELECT table1_id, (SYSDATE - MAX(table2.date_field)) days
 WHERE table1.field1 IS NOT null
 AND table1.table1_id = table2.table1_fk
 AND table2.table3_fk = table3.table3_id
 AND (table3.type = X OR table3.type = Y)
 GROUP BY table1_id) tmp_table
WHERE days >= cutoff
and table1.table1_id = tmp_table.table1_id)

share|improve this answer

answered Mar 27 at 20:06

hbourchihbourchi

18477 bronze badges

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  The sub-sub query is missing a FROM statement, and I changed the SELECT null to SELECT table1.table1_id, but this seems to work! I figured it was going to be something simple, thanks for this.
  
  – caliber
  Mar 27 at 20:54
  

  
  
  
  

add a comment | 

0

Not sure if I understand your question fully but another way to write your query is using EXISTS. something like this:

UPDATE table1
SET table1.field1 = null
WHERE exists 
(SELECT null
 FROM (SELECT table1_id, (SYSDATE - MAX(table2.date_field)) days
 WHERE table1.field1 IS NOT null
 AND table1.table1_id = table2.table1_fk
 AND table2.table3_fk = table3.table3_id
 AND (table3.type = X OR table3.type = Y)
 GROUP BY table1_id) tmp_table
WHERE days >= cutoff
and table1.table1_id = tmp_table.table1_id)

share|improve this answer

answered Mar 27 at 20:06

hbourchihbourchi

18477 bronze badges

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  The sub-sub query is missing a FROM statement, and I changed the SELECT null to SELECT table1.table1_id, but this seems to work! I figured it was going to be something simple, thanks for this.
  
  – caliber
  Mar 27 at 20:54
  

  
  
  
  

add a comment | 

Not sure if I understand your question fully but another way to write your query is using EXISTS. something like this:

UPDATE table1
SET table1.field1 = null
WHERE exists 
(SELECT null
 FROM (SELECT table1_id, (SYSDATE - MAX(table2.date_field)) days
 WHERE table1.field1 IS NOT null
 AND table1.table1_id = table2.table1_fk
 AND table2.table3_fk = table3.table3_id
 AND (table3.type = X OR table3.type = Y)
 GROUP BY table1_id) tmp_table
WHERE days >= cutoff
and table1.table1_id = tmp_table.table1_id)

share|improve this answer

answered Mar 27 at 20:06

hbourchihbourchi

18477 bronze badges

UPDATE table1
SET table1.field1 = null
WHERE exists 
(SELECT null
 FROM (SELECT table1_id, (SYSDATE - MAX(table2.date_field)) days
 WHERE table1.field1 IS NOT null
 AND table1.table1_id = table2.table1_fk
 AND table2.table3_fk = table3.table3_id
 AND (table3.type = X OR table3.type = Y)
 GROUP BY table1_id) tmp_table
WHERE days >= cutoff
and table1.table1_id = tmp_table.table1_id)

share|improve this answer

answered Mar 27 at 20:06

hbourchihbourchi

18477 bronze badges

answered Mar 27 at 20:06

hbourchihbourchi

18477 bronze badges

answered Mar 27 at 20:06

hbourchihbourchi

18477 bronze badges