Make a column unique 1

[yigalm]

Hi,

I need to make an existing column unique im MySQL.

When I say:
alter table pr2_users_values
-> add unique f_login;

It gives me an error at line 2 near ''.

Does that meean that something is missing from the statement? What is missing?

 

[cDevX99]

try this ...
alter table pr2_users_values add unique ( f_login ) ;

THat should do it B-)

 

[yigalm]

Thanx, I got that after hours of trial and error.
The real problem now is the following:

We need to use the REPLACE statement in MySQL to modify data in one MySQL table with data from another.

In a basic case of two dummy tables, where one field in each (table1.field1 and table2.field1) is set to UNIQUE, and running the following querie:
replace table1 (field2)
select field2 from table2;
appends table2.field2 as a separate record in table1 instead of modifying contents of table1.field2 to contents of table2.field2.

What am I doing wrong?

Thank you.

 

[cDevX99]

I did the following experiment ....

mysql> create table testa ( id int not null primary key AUTO_INCREMENT, name char(30) not null )  ;
Query OK, 0 rows affected (0.43 sec)

mysql> create table testb ( id int not null primary key AUTO_INCREMENT, name char(30) not null )  ;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into testa ( name ) values ( 'bob' ), ( 'tom' ), ( 'keith' ) ;
Query OK, 3 rows affected (0.44 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> insert into testb ( name ) values ( 'fred' ), ( 'John' ), ( 'Dave' ), ( 'Phil' ) ;
Query OK, 4 rows affected (0.40 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> select * from testa ;
+----+-------+
| id | name  |
+----+-------+
|  1 | bob   |
|  2 | tom   |
|  3 | keith |
+----+-------+
3 rows in set (0.00 sec)

mysql> select * from testb ;
+----+------+
| id | name |
+----+------+
|  1 | fred |
|  2 | John |
|  3 | Dave |
|  4 | Phil |
+----+------+
4 rows in set (0.00 sec)

mysql> replace into testa select * from testb ;
Query OK, 4 rows affected (0.00 sec)
Records: 4  Duplicates: 3  Warnings: 0

mysql> select * from testa ;
+----+------+
| id | name |
+----+------+
|  1 | fred |
|  2 | John |
|  3 | Dave |
|  4 | Phil |
+----+------+
4 rows in set (0.00 sec)

mysql> select * from testb ;
+----+------+
| id | name |
+----+------+
|  1 | fred |
|  2 | John |
|  3 | Dave |
|  4 | Phil |
+----+------+
4 rows in set (0.00 sec)

mysql>

Hope that helps B-)

 

[cDevX99]

BTW - Something to be careful of, and you can test this, I THINK that the way replace works, is that it does inserts if there is no key match. If a match is found, it does a delete, then an insert. I do not think it does an UPDATE

 

[yigalm]

So, it will only work if the PRIMARY KEYs have an exact match?

The manual says:
"REPLACE works exactly like INSERT, except that if an old record in the table has the same value as a new record on a unique index, the old record is deleted before the new record is inserted."

Does "unique index" mean PRIMARY KEY, and not UNIQUE KEY?

 

[yigalm]

I am now trying to change the primary key.
In one table I was able to do it by setting another field as the primary key, and it automatically set the previous primary key to unique key.
But in the other table, when I try to set another field as a primary key, it gives me an error: "Multiple primary key defined".

How can I change the primary key?

 

[yigalm]

Here I tried it your way, making primary keys for two tables. And it appended again. How is this wrong?

mysql> select * from t1;
+------+------+------+
| str1 | str2 | str3 |
+------+------+------+
| abc | cba | bac |
| zxc | cxz | xzc |
+------+------+------+
2 rows in set (0.01 sec)

mysql> select * from t2;
+------+------+------+
| str1 | str2 | str3 |
+------+------+------+
| abc | poi | iop |
+------+------+------+
1 row in set (0.01 sec)

mysql> replace into t1 (str2) select str2 from t2;
Query OK, 1 row affected (0.01 sec)


mysql> select * from t1;
+------+------+------+
| str1 | str2 | str3 |
+------+------+------+
| abc | cba | bac |
| zxc | cxz | xzc |
| | poi | NULL |
+------+------+------+
3 rows in set (0.01 sec)

mysql> show create table t1;
+-------+--------------------------------
-----------------------------------------
| Table | Create Table

+-------+--------------------------------
-----------------------------------------
| t1 | CREATE TABLE `t1` (
`str1` varchar(30) NOT NULL default '',
`str2` varchar(30) default NULL,
`str3` varchar(30) default NULL,
PRIMARY KEY (`str1`)
) TYPE=MyISAM |
+-------+--------------------------------
-----------------------------------------
1 row in set (0.00 sec)

mysql> show create table t2;
+-------+--------------------------------
-----------------------------------------
| Table | Create Table

+-------+--------------------------------
-----------------------------------------
| t2 | CREATE TABLE `t2` (
`str1` varchar(30) NOT NULL default '',
`str2` varchar(30) default NULL,
`str3` varchar(30) default NULL,
PRIMARY KEY (`str1`)
) TYPE=MyISAM |
+-------+--------------------------------
-----------------------------------------
1 row in set (0.01 sec)

 

[cDevX99]

What is it that you want to happen?

You are starting with this...

mysql> select * from t1;
+------+------+------+
| str1 | str2 | str3 |
+------+------+------+
| abc | cba | bac |
| zxc | cxz | xzc |
+------+------+------+
2 rows in set (0.01 sec)

mysql> select * from t2;
+------+------+------+
| str1 | str2 | str3 |
+------+------+------+
| abc | poi | iop |
+------+------+------+
1 row in set (0.01 sec)


After the replace is this what you hope to end up with?

mysql> select * from t1;
+------+------+------+
| str1 | str2 | str3 |
+------+------+------+
| abc | poi | bac |
| zxc | cxz | xzc |
+------+------+------+
2 rows in set (0.01 sec)

or is this what you are looking for?

mysql> select * from t1;
+------+------+------+
| str1 | str2 | str3 |
+------+------+------+
| abc | poi | iop |
| zxc | cxz | xzc |
+------+------+------+
2 rows in set (0.01 sec)

 

[yigalm]

I only want to change t1.str2 to the value of t2.str2
with the following result:

mysql> select * from t1;
+------+------+------+
| str1 | str2 | str3 |
+------+------+------+
| abc | poi | bac |
| zxc | cxz | xzc |
+------+------+------+
2 rows in set (0.01 sec)

 

[yigalm]

I got it.
When replacing you have to select the fields which you want to replace AND the unique field which you want to use as a common denominator.

 

[cDevX99]

OK, can't do that with a replace, replace replaces the entire row or appends a new one.

 

[yigalm]

I just tried it and it worked.
You CAN replace only one field, leaving the rest of the record intact. You just have to select your unique key together with other fields that you want to modify:

mysql> describe testa;
+-------+----------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+----------------+
| id | int(11) | | PRI | NULL | auto_increment |
| name | char(30) | | | | |
+-------+----------+------+-----+---------+----------------+
2 rows in set (0.00 sec)

mysql> describe testb;
+-------+----------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+----------------+
| id | int(11) | | PRI | NULL | auto_increment |
| name | char(30) | | | | |
+-------+----------+------+-----+---------+----------------+
2 rows in set (0.00 sec)

mysql> select * from testa;
+----+-------+
| id | name |
+----+-------+
| 1 | igor |
| 2 | tom |
| 3 | keith |
+----+-------+
3 rows in set (0.00 sec)

mysql> select * from testb;
+----+-------+
| id | name |
+----+-------+
| 1 | john |
| 2 | tim |
| 3 | joe |
| 4 | Phil |
+----+-------+
4 rows in set (0.01 sec)

mysql> replace into testb (id, name) select id, name from testa;
Query OK, 3 rows affected (0.01 sec)
Records: 3 Duplicates: 3 Warnings: 0

mysql> select * from testb;
+----+-------+
| id | name |
+----+-------+
| 1 | igor |
| 2 | tom |
| 3 | keith |
| 4 | Phil |
+----+-------+
4 rows in set (0.01 sec)

 

[cDevX99]

Yes, but try adding a third column that you want to remain the same, if you "select *" it will be populated from the second tables data, if you only select the first two columns the third will be populated with a null or the columns default value

 

[yigalm]

I guess you are right. It would make sence because REPLACE would delete the entire old record and put the entire new record in it's place. Therefore, if the new record does not have values for some fields, they will be set to default.

I need to update some columns in one table with data from some columns in another, and I am dealing with the replace problem in the following manner:

I am creating a temp table and inserting in it the contents of the destination table (the one that needs to be updated). Then I am deleting all data from the destination table (which is now backed up in the temp table). And, finally, I am inserting the source table joined with the temp table back into the destination table.

The problem is, though, that the destination table contains more records than the source table. Following, if I join them on the basis of a unique identifying field present in both tables, I will be loosing records which were present only in the temp table, but not in the source table.

This is what I mean:

insert into dest (col1, col2, col3)
select source.col1, source.col2, temp.col3
from source, temp
where source.col1 = temp.col1;

temp has more fields than source, but now only the fields that are present in both tables will be copied back to dest.

How should I make my query that all the fields in temp will be copied back to dest, and the fields that are contained in both souce and temp will be copied back jointly?

Thank you

 

[yigalm]

I got it!
I will use one insert like above, to only copy joined records. And I will use another insert with the IGNORE option to copy the rest of the records, ignoring the duplicates.

Thank you, cDevX99 !!!