Solved

Firebird SQL update with inner join

Posted on 2013-11-29
19
10,535 Views
Last Modified: 2014-02-03
The FB SQL statement

 select * from "ERP_Rolls" T3
 INNER JOIN "RollInfo" T2
 ON T3."Roll_ID_Number" = T2."Roll_ID_Number"
 where T3."Delivery_DateFlag" >= '20131001' and
T3."Delivery_DateFlag" <= '20131031'

works and returns 1647 records

then I want to update "ERP_Rolls","Status" in the returned set using the following

UPDATE "ERP_Rolls"
 SET "ERP_Rolls"."Status" = '999'
 where exists (
 select * from "ERP_Rolls" T3
 INNER JOIN "RollInfo" T2
 ON T3."Roll_ID_Number" = T2."Roll_ID_Number"
 where T3."Delivery_DateFlag" >= '20131001' and
T3."Delivery_DateFlag" <= '20131031')

 but the statement wants to update every record in the database,

What am I doing wrong?
0
Comment
Question by:CtexAndy
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 6
  • 3
  • +2
19 Comments
 
LVL 5

Expert Comment

by:Lawrence Barnes
ID: 39686433
I have not used Firebird, but in t-SQL the update query is not linked to the select query.  This will connect them.


UPDATE x
 SET x."Status" = '999'
From "ERP_Rolls" x
 INNER JOIN "RollInfo" T2
 ON x."Roll_ID_Number" = T2."Roll_ID_Number"
 where x."Delivery_DateFlag" >= '20131001' and
x."Delivery_DateFlag" <= '20131031'
0
 

Author Comment

by:CtexAndy
ID: 39686513
Thanks for the response but it falls over at the 'From' statement

ISC ERROR CODE:335544569

ISC ERROR MESSAGE:
Dynamic SQL Error
SQL error code = -104
Token unknown - line 3, char 2
From

Statement: UPDATE x
 SET x."Status" = '999'
 From "ERP_Rolls" x
 INNER JOIN "RollInfo" T2
 ON x."Roll_ID_Number" = T2."Roll_ID_Number"
 where x."Delivery_DateFlag" >= '20131001' and
x."Delivery_DateFlag" <= '20131031'
0
 
LVL 19

Expert Comment

by:NickUpson
ID: 39686878
in your original update as said the update and select are not linked, so each row the select is executed and returns true, so the update is done.

try this

UPDATE "ERP_Rolls"
 SET "ERP_Rolls"."Status" = '999'
 where T3."Delivery_DateFlag" >= '20131001' and T3."Delivery_DateFlag" <= '20131031'
exists (
 select 1 from "RollInfo" T2  ON "Roll_ID_Number" = T2."Roll_ID_Number"
)

BTW: unless you have used case-sensitive column names the quotes are not required
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 32

Expert Comment

by:awking00
ID: 39687043
NickUpson solution should work but needs AND keyword -
...
AND exists( select ...
0
 
LVL 19

Expert Comment

by:NickUpson
ID: 39687428
yup, my bad
0
 

Author Comment

by:CtexAndy
ID: 39692213
And On etc does not work here either it seems that the link between what to update and the group to be updated are not being linked!

The update statement works if kept simple as does the join statement.... any more ideas please?
0
 
LVL 32

Expert Comment

by:awking00
ID: 39692398
Can you post some sample data to include records that should be updated (no need for all 1647) and records that do not get updated and your expected results?
0
 

Author Comment

by:CtexAndy
ID: 39702406
Travelling at the moment but will pit something together ASAP thanks
0
 
LVL 14

Expert Comment

by:Pierre Cornelius
ID: 39723412
so you want to update status of ERP_Rolls for items found in RollInfo based on specific date range?

I assume
- Roll_ID_Number is the primary key
- Delivery_DateFlag is a text field in which dates are stored in format yyyymmdd


Try:

update ERP_Rolls set Status = '999'
where Roll_ID_Number in (select distinct Roll_ID_Number from RollInfo where "Delivery_DateFlag" >= '20131001' and "Delivery_DateFlag" <= '20131031')

Open in new window

0
 
LVL 19

Expert Comment

by:NickUpson
ID: 39723555
you need to be aware that there is a maximum of 1500 items inside an 'in' statement, so if the inner select returns more rows than that it will fail
0
 
LVL 14

Expert Comment

by:Pierre Cornelius
ID: 39723644
Thanks Nick, I did not know that.

P.S. You are missing the T3 alias in your example.
0
 

Author Comment

by:CtexAndy
ID: 39741397
The records that should be updated number 1605 so exceeds this limit with this in mind I reduced the date range to include only the 31/10/2013 which should yield only 592 records but it still tries to update 54774 ?
Join-31st-only.jpg
Select-31st-only.jpg
0
 
LVL 14

Expert Comment

by:Pierre Cornelius
ID: 39742812
Is Interactive SQL returning the correct number of "Rows affected"?

try the following:

1. Get starting count

how many records have status = '999' before running the update?
select count(1) from ERP_Rolls where status = '999'

Open in new window


2. How many records to update?

select count(1) from ERP_Rolls R where R."Delivery_DateFlag" >= '20131001' and R."Delivery_DateFlag" <= '20131031'
and exists (select 1 from "RollInfo" I where I."Roll_ID_Number" = R."Roll_ID_Number")

Open in new window


3. Update records

UPDATE ERP_Rolls R set status = '999' 
where R."Delivery_DateFlag" >= '20131001' and R."Delivery_DateFlag" <= '20131031'
and exists (select 1 from "RollInfo" I where I."Roll_ID_Number" = R."Roll_ID_Number")

Open in new window


4. Get ending count

how many records have status = '999' after running the update?
select count(1) from ERP_Rolls where status = '999'

Open in new window


5. Expected updates = actual updates?

count per (4) - count per (1) = count per (2) ?

Possible reason
If the Roll_ID_Number values are not unique in the RollInfo table that could be the reason as the stream created during the join would include each instance. e.g. assume Roll_ID_Number 12345 appears 5 times in the RollInfo table then the joined stream would include it 5 times, however, the only record to update would be the unique one in the ERP_Rolls table, so in this example Interactive SQL may be reporting 5 records updated (the 5 in the joined stream) instead of the 1 record that is actually updated in the ERP_Rolls table. Perhaps the RollInfo data should be reduced to unique values before joining (not sure if it will help and can't test on my side - don't have your data) e.g.
...
and exists (select distinct I.Roll_ID_Number from "RollInfo" I where I."Roll_ID_Number" = R."Roll_ID_Number")

Open in new window

0
 

Author Comment

by:CtexAndy
ID: 39743358
The "Roll_ID_Number" is generated via an SAP system so is unique.

I have chopped the database down to a manageable size and have attached it. (ctexsmall.fdb)

I am using Firebird 2.5.1 (32bit) and have included the security2.fdb file
user sysdba
pw admin234

rename both *.fdb
CTEXSMALL.doc
security2.doc
0
 
LVL 14

Expert Comment

by:Pierre Cornelius
ID: 39767612
Hi there. Sorry for late response. Been on holiday :)

Just because it is generated via a SAP system does not mean the values are unique.

I downloaded your db and noticed the following:
1) "ERP_Rolls"."Roll_ID_Number" is not the primary key and in fact not unique, there are 858 records where the Roll_ID_Number is not unique

2) You don't have an index on the Roll_ID_Number in ERP_Rolls nor in RollInfo. This will make joins very slow.

3)  "RollInfo"."Roll_ID_Number" is not the primary key and in fact not unique, there are 501 records where the Roll_ID_Number is not unique

So seems to me I was on to something in my previous comment :)
0
 
LVL 14

Accepted Solution

by:
Pierre Cornelius earned 500 total points
ID: 39767623
So based on my previous comment I do the following:

1    Get starting count
  select count(1) from "ERP_Rolls" where "Status" = '999'
 
  => zero                                            


2    How many records to update?
   select count(1) from "ERP_Rolls" R where R."Delivery_DateFlag" >= '20131001' and R."Delivery_DateFlag" <= '20131031'
  and exists (select 1 from "RollInfo" I where I."Roll_ID_Number" = R."Roll_ID_Number")
 
  => 1646


3    Update records
  UPDATE "ERP_Rolls" R set "Status" = '999'
  where R."Delivery_DateFlag" >= '20131001' and R."Delivery_DateFlag" <= '20131031'
  and exists (select 1 from "RollInfo" I where I."Roll_ID_Number" = R."Roll_ID_Number")

  => 1646 records updated (As reported by FlameRobin - the Database Admin Tool I use )


4    Get ending count
  select count(1) from ERP_Rolls where status = '999'
 
  => 1646


5    Expected updates = actual updates?
    count per (4) - count per (1) = count per (2) ?
    1646 - 0 = 1646
    => true, so all good :)
0
 

Author Comment

by:CtexAndy
ID: 39789717
hi that looks good thanks so much I have been travelling and will look at this over the weekend
0
 

Author Closing Comment

by:CtexAndy
ID: 39831465
thank you soooooo much
0
 
LVL 14

Expert Comment

by:Pierre Cornelius
ID: 39831638
Glad to help. Good luck!
0

Featured Post

Will your db performance match your db growth?

In Percona’s white paper “Performance at Scale: Keeping Your Database on Its Toes,” we take a high-level approach to what you need to think about when planning for database scalability.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Azure Functions is a solution for easily running small pieces of code, or "functions," in the cloud. This article shows how to create one of these functions to write directly to Azure Table Storage.
Lotus Notes has been used since a very long time as an e-mail client and is very popular because of it's unmatched security. In this article we are going to learn about  RRV Bucket corruption and understand various methods to Fix "RRV Bucket Corrupt…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
This is a high-level webinar that covers the history of enterprise open source database use. It addresses both the advantages companies see in using open source database technologies, as well as the fears and reservations they might have. In this…
Suggested Courses

623 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question