How do I use this result to be able to construct and execute a statement as follows?:
Code:
SELECT * FROM sources WHERE [description] IS NULL AND [pricedata] IS NULL AND [tablename] IS NULL AND [reference] IS NULL;
...
Code:
mysql>
mysql> --
mysql> select column_name
-> from columns
-> where table_name = 'sources'
-> and column_key = '';
+-------------+
| column_name |
+-------------+
| description |
| pricedata |
| tablename |
| reference |
+-------------+
4 rows in set (0.04 sec)
mysql>
mysql>
mysql> --
mysql> select concat ('select * from sources where ', substring(group_concat(concat('AND ',column_name,' IS NULL') separator ' '),5), ';') as x
-> from columns
-> where table_name = 'sources'
-> and column_key = '';
+--------------------------------------------------------------------------------------------------------------------+
| x |
+--------------------------------------------------------------------------------------------------------------------+
| select * from sources where description IS NULL AND pricedata IS NULL AND tablename IS NULL AND reference IS NULL; |
+--------------------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)
mysql>
mysql>
mysql>
mysql> --
mysql> -- Use a variable instead of hard-coding the table name
mysql> --
mysql> set @x = 'sources';
Query OK, 0 rows affected (0.00 sec)
mysql>
mysql>
mysql> --
mysql> select concat ('select * from ', @x, ' where ', substring(group_concat(concat('AND ',column_name,' IS NULL') separator ' '),5), ';') as x
-> from columns
-> where table_name = @x
-> and column_key = '';
+--------------------------------------------------------------------------------------------------------------------+
| x |
+--------------------------------------------------------------------------------------------------------------------+
| select * from sources where description IS NULL AND pricedata IS NULL AND tablename IS NULL AND reference IS NULL; |
+--------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql>
mysql>
---------- Post updated at 12:24 AM ---------- Previous update was at 12:15 AM ----------
Or another one:
Code:
mysql>
mysql> --
mysql> select concat ('select * from ', @x, ' where ', group_concat(column_name separator ' is null and '), ' is null;') as x
-> from columns
-> where table_name = @x
-> and column_key = '';
+--------------------------------------------------------------------------------------------------------------------+
| x |
+--------------------------------------------------------------------------------------------------------------------+
| select * from sources where description is null and pricedata is null and tablename is null and reference is null; |
+--------------------------------------------------------------------------------------------------------------------+
1 row in set (0.04 sec)
mysql>
mysql>
---------- Post updated at 12:53 AM ---------- Previous update was at 12:24 AM ----------
If you want to do something similar for a bunch of tables, then you could assign the table list to a variable and use it in your query.
Over here, I've generated relevant queries for a few tables of the Sakila database:
Code:
mysql>
mysql> --
mysql> set @set_of_tables = 'actor,city,country';
Query OK, 0 rows affected (0.00 sec)
mysql> set @schema = 'sakila';
Query OK, 0 rows affected (0.00 sec)
mysql>
mysql> --
mysql> select table_name, column_name, column_key
-> from columns
-> where find_in_set (table_name, @set_of_tables)
-> and table_schema = @schema;
+------------+-------------+------------+
| table_name | column_name | column_key |
+------------+-------------+------------+
| actor | actor_id | PRI |
| actor | first_name | |
| actor | last_name | MUL |
| actor | last_update | |
| city | city_id | PRI |
| city | city | |
| city | country_id | MUL |
| city | last_update | |
| country | country_id | PRI |
| country | country | |
| country | last_update | |
+------------+-------------+------------+
11 rows in set (0.04 sec)
mysql>
mysql> --
mysql> select concat ('select * from ', table_name, ' where ', group_concat(column_name separator ' is null and '), ' is null;') as x
-> from columns
-> where find_in_set (table_name, @set_of_tables)
-> and table_schema = @schema
-> and column_key != 'PRI'
-> group by table_name;
+---------------------------------------------------------------------------------------------+
| x |
+---------------------------------------------------------------------------------------------+
| select * from actor where first_name is null and last_name is null and last_update is null; |
| select * from city where city is null and country_id is null and last_update is null; |
| select * from country where country is null and last_update is null; |
+---------------------------------------------------------------------------------------------+
3 rows in set (0.18 sec)
mysql>
mysql>
I want to collect the database tables(Ex: Emp,Sal,Bonus...etc)
in a file & give this file at the command prompt, when prompted for the tables list at command prompt. How can i do that ?
Ex:- Import jason/jason1 tables=emp,sal,bonus log=j1.log
i want to change this into
Ex:- Import... (3 Replies)
Hi all, i would like to set a cronjob to back up my db tables.
I have written the par file, i would like to know how can i execute a exp command in a shell script.
my par file userid=admin/password
file=backup.dmp
log=backupLOG.log
tables=
(Tables 1,
Tables 2)My cron job script will be... (1 Reply)
I am coding shell script.
I need to connect to different databases like DB2, Oracle and Sybase.
I would then need to query tables where it has all the groups, users for that database.
I would also need who has what kind of permissions.
EG: I know for DB2 some TABAUTH table needs to be... (0 Replies)
I'm pretty new to the database world and I've run into a mental block of sorts. I've been unable to find the answer anywhere. Here's my problem: I have several tables and everything is as normalized as possible (as I've been lead to understand normalization.) Normalization has lead to some... (1 Reply)
Hi all,
I am executing shell script in which I am using SQLLDR
In this SQLLDR I am passing text file having PL/SQL script. This script will produce some formated output, this output I have to spool in another text file. Currently I have given this in script file as following
Spool... (2 Replies)
I haven't coded in ksh in forever and this script is proving me not a great coder. I keep getting unexpected end of file when I try to execute this...Line 94 is the last line of the code..
#!/usr/bin/ksh
. /home/oraprod/.bash_profile
DBSID=prod # Database sid
CONNECTSTR=apps/xxxxxxxx@prod #... (6 Replies)
Hi,
Please let me know if you have any thoughts on how to read a table that has all the oracle sql files or shell scripts at the job and step level to identify all the tables that does merge, update, delete, insert, create, truncate, alter table (ALTER TABLE XYZ RENAME TO ABC) and call them out... (1 Reply)
08-24-2013