CODEWITHSUNDEEP
mysqlsqlsql-update
Steve Price
Steve Price

Reputation: 600

Update a column using values from another column that match a specific criteria

I want to drop all the entries from the field customers_fax and then move all numbers beginning 07 from the customers_telephone field to the customers_fax field.

The table structure is below

CREATE TABLE IF NOT EXISTS `zen_customers` (
`customers_id` int(11) NOT NULL,
`customers_gender` char(1) NOT NULL DEFAULT '',
`customers_firstname` varchar(32) NOT NULL DEFAULT '',
`customers_lastname` varchar(32) NOT NULL DEFAULT '',
`customers_dob` datetime NOT NULL DEFAULT '0001-01-01 00:00:00',
`customers_email_address` varchar(96) NOT NULL DEFAULT '',
`customers_nick` varchar(96) NOT NULL DEFAULT '',
`customers_default_address_id` int(11) NOT NULL DEFAULT '0',
`customers_telephone` varchar(32) NOT NULL DEFAULT '',
`customers_fax` varchar(32) DEFAULT NULL,
`customers_password` varchar(40) NOT NULL DEFAULT '',
`customers_newsletter` char(1) DEFAULT NULL,
`customers_group_pricing` int(11) NOT NULL DEFAULT '0',
`customers_email_format` varchar(4) NOT NULL DEFAULT 'TEXT',
`customers_authorization` int(1) NOT NULL DEFAULT '0',
`customers_referral` varchar(32) NOT NULL DEFAULT '',
`customers_paypal_payerid` varchar(20) NOT NULL DEFAULT '',
`customers_paypal_ec` tinyint(1) unsigned NOT NULL DEFAULT '0'
 ) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=18346 ;

Dropping any existing data is simple enough as I will just do

UPDATE zen_customers SET customers_fax = ''

I've no idea how to move only numbers starting with 07 to the customers_fax field ensuring they stay with the relevant customers_id.

Is there a simple way to do this as an SQL query only?

Upvotes: 1

Views: 134

Answers (4)

jpw
jpw

Reputation: 44891

You could use a case expression to conditionally set customers_fax to customers_telephone if customers_telephone starts with 07, and '' if it doesn't:

UPDATE zen_customers 
SET customers_fax = CASE 
                      WHEN customers_telephone LIKE '07%' THEN customers_telephone 
                      ELSE '' 
                    END;

This would save you from having to run two different updates.

Upvotes: 0

d34n5
d34n5

Reputation: 1318

This should work:

UPDATE zen_customers 
SET customers_fax = customers_telephone 
WHERE customers_telephone LIKE '07%';

But if you want to test on one customer to be sure it does what you expect, you can always run this update query on a specific user that have a '07' telephone number:

UPDATE zen_customers 
SET customers_fax = customers_telephone 
WHERE customers_id = N;

Where N is an integer.

Upvotes: 1

mynawaz
mynawaz

Reputation: 1594

UPDATE zen_customers a, zen_customers b 
SET a.customers_fax=b.customers_telephone 
WHERE 
    a.customers_id=b.customers_id 
    AND b.customers_telephone LIKE '07%'

Upvotes: 0

Ike Walker
Ike Walker

Reputation: 65547

Try something like this:

UPDATE zen_customers 
SET customers_fax = customers_telephone
WHERE customers_telephone like '07%'

Upvotes: 1

Related Questions