Solved

Firebird SQL update with inner join

Posted on 2013-11-29
19
9,425 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
  • 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
 
LVL 31

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 31

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
Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

 
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

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

In my programming career I have only very rarely run into situations where operator overloading would be of any use in my work.  Normally those situations involved math with either overly large numbers (hundreds of thousands of digits or accuracy re…
Never store passwords in plain text or just their hash: it seems a no-brainier, but there are still plenty of people doing that. I present the why and how on this subject, offering my own real life solution that you can implement right away, bringin…
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
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…

744 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now