Multiple Row Updates in MySQL

CREATE TABLE  `testimonial_setting` (
  `testimonial_setting_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `testimonial_label` varchar(255) NOT NULL,
  `testimonial_key` varchar(45) NOT NULL,
  `testimonial_value` varchar(45) NOT NULL,
  PRIMARY KEY (`testimonial_setting_id`)
);

Table :-  testimonial_setting

testimonial_setting_id, testimonial_label,    testimonial_key, testimonial_value

            1,                       'Background Color',    'bgcolor',            ''

            2,                        'Text Color',               'textcolor',          ''

            3,                      'Heding Text Color',    'headtextcolor',    ''

            4,               'Heding Background Color', 'headbgcolor',    ''

Query :-

UPDATE `testimonial_setting` SET
`testimonial_value` = IF(`testimonial_key`="bgcolor","#ff00ff",`testimonial_value`),
`testimonial_value` = IF(`testimonial_key`="textcolor","#ffffff",`testimonial_value`),
`testimonial_value` = IF(`testimonial_key`="headtextcolor","#ff0000",`testimonial_value`),
`testimonial_value` = IF(`testimonial_key`="headbgcolor","#ff0fff",`testimonial_value`);

After update table :-  testimonial_setting

testimonial_setting_id, testimonial_label,    testimonial_key, testimonial_value

            1,                       'Background Color',    'bgcolor',            '#ff00ff'

            2,                        'Text Color',               'textcolor',          '#ffffff'

            3,                      'Heding Text Color',    'headtextcolor',    '#ff0000'

            4,               'Heding Background Color', 'headbgcolor',    '#ff0fff'

Import a SQL file using the command line in MySQL

A common use of mysqldump is for making a backup of an entire database:

  shell> mysqldump db_name > backup-file.sql

 You can load the dump file back into the server like this:

  UNIX
shell> mysql db_name < backup-file.sql

  Same in Windows comand prompt
mysql -p -u[user] [database] < backup-file.sql

  PowerShell
C:\> cmd.exe /c "mysql -u root -p db_name < backup-file.sql"

  MySQL command line
mysql> use db_name; mysql> source backup-file.sql;

Parent child relationship mysql query

Create table like this :-

CREATE TABLE `nodeslist` (

  `NodeID` mediumint(9) NOT NULL AUTO_INCREMENT,

  `NodeTypeID` tinyint(3) unsigned NOT NULL,

  `NodeCode` char(20) NOT NULL,

  `NodeLocationName` char(75) NOT NULL,

  PRIMARY KEY (`NodeID`),

) ;

Here this query to select tree struture data :-

 SELECT
    p.NodeID as parent_id,
    c1.NodeID as child_id_1,
    c1.ParentNode as parent_id_1,
    c2.NodeID as child_id_2,
    c2.ParentNode as parent_id_2,
    c3.NodeID as child_id_3,
    c3.ParentNode as parent_id_3
FROM
    nodeslist p
LEFT JOIN nodeslist c1
    ON c1.ParentNode = p.NodeID
LEFT JOIN nodeslist c2
    ON c2.ParentNode = c1.NodeID
LEFT JOIN nodeslist c3
    ON c3.ParentNode = c2.NodeID
WHERE
    p.ParentNode=0;

Select all child and subchild for particular Parent ID:-

SELECT NodeID,ParentNode,NodeTypeID

FROM nodeslist

WHERE NodeID = 1

OR ParentNode = 1

OR ParentNode IN (SELECT NodeID

    FROM nodeslist

    WHERE ParentNode = 1 OR

    ParentNode IN (SELECT NodeID

    FROM nodeslist

    WHERE ParentNode = 1)

  );