UPDATE查询中的通配符列名称

Question

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?

Answer 1

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';