Assuming I have a SQL table called techsched setup as below but the 800,900 etc. etc. went on till 2300, each representing a one hour block of time. a six character alphanumeric character (TPI232) represents a ticket number assigned to a tech and the ticket number is duplicated across each block of time the tech is scheduled for. I need to run a SQL query that can find a ticket number regardless of where it is located in the TABLE and set the value of the row/column where it is found to NULL or BLANK
Tech date 800 900 1000 1100 1200 John Doe 05-01-15 DSA123 DSA123 DSA123 Mike Doe 05-01-15 FGG342 FGG342 Bill Doe 05-01-15 Steve Doe 05-01-15 TPI232 TPI232 TPI232
I know this below will not and does not work but its the best way to demonstrate what I'm trying to do:
UPDATE techsched SET wildcard_column_name='' WHERE wildcard_column_name='FGG342'
I don't know where the record number may occur at in the table, so how can this be accomplished?
Create a stored procedure like this and tweak it to suit your case:
delimiter $$
drop procedure if exists clear_values$$
create procedure clear_values(subject char(10))
begin
declare finished int default 0;
declare colname varchar(100);
-- cursor
declare cur_columns cursor for
select column_name
from information_schema.columns
where table_name = 'test'
and data_type = 'char'
and character_maximum_length = 10;
-- data type and length matches the field info
-- in my table
-- handler for when we run out of records to read
declare continue handler for not found
set finished = 1;
open cur_columns;
reading: loop
-- retrieve data until end of records
fetch cur_columns into colname;
if finished = 1 then
leave reading;
end if;
-- create text that will update column's value
set @statement = concat(
'update test ',
'set `', colname, '` = \'\' ',
'where `', colname, '` = \'', subject, '\''
);
-- create a prepared statement from the text
-- and execute it
prepare stmt from @statement;
execute stmt;
deallocate prepare stmt;
end loop reading;
close cur_columns;
end$$
delimiter ;
When you get a chance, think about normalizing the table somewhat assuming this is a small project:
create table techs (
id int auto_increment primary key,
tech varchar(50)
);
create table schedules (
id int auto_increment primary key,
tech_id int not null,
sched datetime not null,
ticket char(6),
constraint fk_schedules_techs_tech_id
foreign key (tech_id)
references techs (id),
constraint uk_schedules_tech_id_sched
unique (tech_id, sched)
);
insert into techs (tech) values
('Joe'),
('Matt');
insert into schedules (tech_id, sched, ticket) values
(1, '2015-05-01 08:00:00', ''),
(1, '2015-05-01 09:00:00', ''),
(1, '2015-05-01 10:00:00', 'DSA123'),
(2, '2015-05-01 08:00:00', 'FGG324'),
(2, '2015-05-01 09:00:00', 'FGG324'),
(2, '2015-05-01 10:00:00', '');
http://sqlfiddle.com/#!9/19bc3/1
Now when you have to clear out ticket where the ticket was FGG324, you can type:
update schedules set ticket = '' where ticket = 'FGG324';
I would suggest you to redesign the structure of the table. For instance, columns could be "tech, date, hour, ticket".