![]() Here p_id is used from products table and used in stock table. Syntax: CREATE TABLE newtablename AS TABLE. In both tables columns are not matching but one column only is same. To copy only table structure without data, WITH NO DATA clause can be used with CREATE TABLE AS statement. ON DUPLICATE KEY UPDATE mark=200,gender=student.gender Transfer data from one table to other SELECT id,name,class,mark,gender FROM student WHERE class='Four' Updating more than one column INSERT INTO student2(id,name,class,mark,gender) ![]() The above query will insert the record and update the mark column to 5, this way we can update records using on duplicate key command. #1062 - Duplicate entry '3' for key 'PRIMARY'įROM student WHERE id=3 ON DUPLICATE KEY UPDATE mark=5 This query will generate the error like this as id =3 is already exist in student2 table and that will violate the unique constraint. SELECT id,name,class,mark,gender FROM student WHERE student.id=3 createdb mydb -E UTF8 (you dont have to specify UTF8 encoding, but I always do) Then: psql -d mydb -f db.sql. in your list, just without this consideration: but this requires the indexes on one table being copied to the other. Copy db.sql to the new server (specific command depends on OS) Go to the new server. If you don't have other objects depending on the tables (like views and foreign keys), it's a simple operation to drop the existing table dest and just rename the new table src. INSERT INTO student2 (id,name,class,mark,gender) Heres a couple of commands from the above link: Create the backup: pgdump mydb > db.sql. Without using REPLACE command we can also use ON DUPLICATE KEY UPDATE to update the student2 table.Īfter keeping all the records in student2 tables let us try this query TRUNCATE student2 on Duplicate Key Update In above query we have not included gender column and this column accepts NULL as default value ( or it has some other default value ).Īny time to remove all data ( NOT structure ) from student2 table use truncate query INSERT INTO student2 (id,name,class,mark) WE can use selected columns to insert the records, here is an example. REPLACE INTO student2 SELECT * FROM student WHERE class = 'Four' I want to copy data from every table in db1 into db2. Here if we want to update student2 table then we have to use REPLACE command in place of INSERT command. Please note that if the student2 table is not empty then the unique constraints for the field id will not allow duplicate id so data will not be updated. INSERT INTO student2 SELECT * FROM student WHERE class = 'Four' We can restrict (filter ) the data we want to export by adding where clause to the query like this. With this command we will copy all data from student to student2 table ![]() INSERT INTO student2 SELECT * FROM student Now we will apply this sql command to export data from student table to student2 table Related Tutorials Copy data to new table downloading records to CSV file SQL Rename table Student table with 35 records
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |