Page 1 of 1

MYSQL Update

Posted: Mon May 13, 2013 3:51 am
by leewad
HI

Im using the following to update databases

Code: Select all

MYSQL_QUERY( "insert into $table1 SELECT * FROM $table2 where 1 ");    
There are 24 fields in each table, my problem is if a reference number ( ie. R12121 ) exists in table1 i want it to update and not add new record, how can this be done?

Re: MYSQL Update

Posted: Mon May 13, 2013 4:15 am
by requinix
a) Modify the SELECT to do an outer JOIN against the destination table and only keep the null rows
b) Add a WHERE where the field is NOT IN the other table (don't do this if the tables are large)
c) REPLACE INTO

Re: MYSQL Update

Posted: Mon May 13, 2013 4:19 am
by leewad
sorry but do you have an example?

Re: MYSQL Update

Posted: Mon May 13, 2013 1:51 pm
by requinix
(a) is probably the overall best solution.

Put it this way: can you write a SELECT query that retrieves exactly what you want to insert? Start with the $table2 and LEFT JOIN $table1 on a matching reference number. Since it's a LEFT join if there's no matching row in $table1 then you'll get back a bunch of NULLs. Then limit the query to only those rows.

Code: Select all

SELECT * FROM $table2 t2
JOIN $table1 t1 ON matching reference numbers
WHERE t1.primary key or really any column at all IS NULL
Then stick the INSERT INTO back in front.

Re: MYSQL Update

Posted: Tue May 14, 2013 2:03 am
by leewad
Thanks for your reply requinix but sorry im not too good with mysql ( table1 is called portal and table 2 is called portal2 and field for the match is called reference )

What is the query to use?

Code: Select all

insert into portal2 SELECT * FROM table1 
JOIN portal2  ON matching `reference`
WHERE reference IS NULL

Re: MYSQL Update

Posted: Tue May 14, 2013 3:18 am
by requinix
You really should learn at least some of SQL.

1. "matching reference numbers". You have two tables, both with a reference number column. What do you write to check if the values match?
2. "primary key". What is the primary key of the table?