CODEWITHSUNDEEP
mysql
Stanley Ngumo
Stanley Ngumo

Reputation: 4239

Moving Data from one column to another similar column in another table MYSQL

I am currently working on a webbased systen using a Mysql db.

I realised that I had initially set up the columns within the tables incorrectly and

I now need to move the data from one table column (receiptno) in table (clients) into a similar table column(receiptno) in table (revenue).

I am still quite inexperienced with Mysql and therefore I dont know the the mysql syntax to accomplish this.

Can I get some help on it.

Thanks

Upvotes: 5

Views: 9455

Answers (3)

user1351763
user1351763

Reputation: 126

Same smell, different odor to eggyal's answer, this works in Oracle and Postgress so your mileage may vary.

UPDATE revenue t1 SET receiptno = (
  SELECT receiptno FROM clients t2 WHERE t2.client_id = t1.revenue_id
);

You will have to adjust the where clause to suit your needs ...

Upvotes: 5

Gregology
Gregology

Reputation: 1725

INSERT INTO newtable (field1, field2, field3)
SELECT filed1, field2, field3
FROM oldtable

Upvotes: 1

eggyal
eggyal

Reputation: 125845

If you simply wanted to insert the data into new records within the revenue table:

INSERT INTO revenue (receiptno) SELECT receiptno FROM clients;

However, if you want to update existing records in the revenue table with the associated data from the clients table, you would have to join the tables and perform an UPDATE:

UPDATE revenue JOIN clients ON **join_condition_here**
SET    revenue.receiptno = clients.receiptno;

Learn about SQL joins.

Upvotes: 13

Related Questions