CODEWITHSUNDEEP
mysql
Juddling
Juddling

Reputation: 4690

MySQL grouping columns

is there any way i can group several of my columns together?

in one for my scripts i need to set several of my columns to the same value, is there a better way than typing out all the column names in my update query?

thanks

Upvotes: 0

Views: 86

Answers (4)

MindStalker
MindStalker

Reputation: 14864

If these columns MUST always have the same value (that seems pointless) you could set an UPDATE TRIGGER on the column

delimiter |
CREATE TRIGGER somename BEFORE INSERT ON table1
 FOR EACH ROW BEGIN
  SET table1.col2=NEW.col1, table1.col3=NEW.col1;
END;

delimiter ;

But if that was the case, it would really make sense to just use ONE column instead of three.

Or, if its a Rights issue (this user shouldn't have the permission to make the columns different) you could build a Stored Procedure for doing the update

delimiter |
CREATE STORED PROCEDURE somename(IN val INT,IN whereval INT)
 BEGIN
  UPDATE table1 SET table1.col1=val, table1.col2=val, table1.col3=val
    WHERE table1.id=whereval;
END;

delimiter ;

Upvotes: 1

Trevor
Trevor

Reputation: 6689

Update syntax for MySQL requires each column to be named explicitly

update table set a = 'value', b = 'value'

Upvotes: 0

Oded
Oded

Reputation: 499002

No, this is not possible.

See the syntax for update:

...
SET col_name1={expr1|DEFAULT} [, col_name2={expr2|DEFAULT}
...

Upvotes: 0

Quassnoi
Quassnoi

Reputation: 425371

You'll need to type each column's name, there is no workaround.

UPDATE  mytable
SET     col1 = 1,
        col2 = 1,
        …

Upvotes: 0

Related Questions