• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

Using MINUS of MySQL with a table & set of input parametes.

 
Ranch Hand
Posts: 83
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
I am facing a unique problem in mysql.
I am posting this problem in this section because it says JDBC + some brushup of SQL.

Firstly i am working on a Apps written in java & interacts with MySQL.
Heres a table EMPLOYEE

ID , Name
1,qqq
2,www
4,rere
6,gerwwe
7,gegwert
8,gsfdjg;

I have a set of input parameters. These are nothing but id of a table EMPLOYEE_CACHE (1,2,3,4,5,6,7)

I want to write a query such that i get output as 3,5
You must have guessed correctly, i want to sync EMPLOYEE_CACHE with EMPLOYEE. I have code that handles INSERT & UPDATE smoothly. Just want to add something that detects delete in master table and update the cache table
My best shot is it can be done using MINUS in mysql. But again MINUS needs 2 tables to work on. And here i have 1 table and a set of parameters.
How can i form my query here
 
Bartender
Posts: 2407
36
Scala Python Oracle Postgres Database Linux
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
I'm not a MySQL developer, but could you use database triggers on the master table to pass changes on to the "cache" table?

Also I don't quite understand the purpose of your EMPLOYEE_CACHE table: why do you need it at all?

You can do a MINUS e.g by comparing the list of IDs in each table, provided the ID for a given employee record never changes. But this will have to keep reading all the IDs in the two tables every time you want to check them, and that seems like a lot of unnecessary work.

If you have to keep the two tables in sync like this, use triggers.

You could also look at table replication if it's really vital to have two synchronised copies of the tables, but you'll need to talk to your DBA about this.

Or better yet, just have one table instead!
 
Don't get me started about those stupid light bulbs.
reply
    Bookmark Topic Watch Topic
  • New Topic