Lookup column name in UPDATE query
Assuming I have a SQL table called techsched
like below but 800 900 etc. etc. lasted until 2300, each of which was one hour. an alphanumeric character with six characters (TPI232) represents the ticket number assigned to the technology, and the ticket number is duplicated every time block for which the process is scheduled. I need to run a SQL query that can find the ticket number no matter where it is in the TABLE and set the row / column value where it is found to be 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 won't and won't 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 in the table might be, so how can this be done?
source to share
Create a stored procedure like this and customize it according to 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 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 the chance, consider normalizing your table, assuming it's 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 need to clear the ticket where the ticket was FGG324 you can enter:
update schedules set ticket = '' where ticket = 'FGG324';
source to share