Insert and update between two tables help

[bnovak]

I think this should be an easy one for someone smarter than me to figure out. I'm trying to do this in phpmyadmin for now, but will convert it to a php function once I get it testing and working.

 

can't do show create table as it's being auto-generated from a user system.

 

basically two tables:

 

profile

id(PK), stateLicenses,additionalLicenses (there's more but for this instance I'm not worried about the other fields)

 

and

state_licenses

id(pk), selectStates,selectSecondary

 

basically want to make a backup of the id,stateLicenses and additionalLicense fields from the profile table to the state_licenses table.

 

If I start with an EMPTY state_licenses table, I can insert all of the details I need into this table with the

 

 INSERT INTO state_licenses (id,selectStates,selectSecondary) SELECT id,stateLicenses,additionalLicenses FROM profile 

 

I know there needs to be an

 

UPDATE state_licenses SET id,selectStates,selectSecondary='profile.id,profile.stateLicenses,profile.additionalLicenses' where ??? 

 

It's that update line that I can't figure out and the tutorials I've found are all for updating inside a table, not updating from another table.

 

what I would like to do is update selectState and selectSecondary in the state_licenses table if there is already an id in the table, and if not, insert a new record with the id, stateLicenses, additionalLicenses from the profile field (to cover increasing profile id's)

 

basically - if state_license(id) is not null update record, else insert the new record.

 

for the life of me I can't figure out how to update the pre-existing records based on the info from the profile table and I'm (obviously) green with coding this stuff.

 

does this make any sense?

 

Unfortunately, I can't come up with any good work around. Basically the member system I'm using is resetting my column properties to VARCHAR(150) when I need TEXT so I need a way to make a backup of user generated info.

 

Any help is greatly appreciated.

 

Thank

-BN

 

[dabaR]

Hi there,

 

I would probably do

$row_exists = db_query("SELECT CASE WHEN EXISTS(SELECT * FROM profile WHERE id = " . db_quote($id) . ") THEN 1 ELSE 0 END");
if ($row_exists) {
  db_query("UPDATE state_licenses SET selectStates = profile.stateLicenses, selectSecondary = profile.additionalLicenses FROM profile WHERE state_licenses.id = profile.id AND profile.id = " . db_quote($id));
} else {
  //insert, you know that one.
}

 

It's called an UPDATE...FROM

[fenway]

There is no "update from".

[dabaR]

Interesting, I did not know, there totally is one in the Microsoft SQL server's SQL implementation, so I figured there would be one in MySQL.

 

This page talks about that, maybe that would help: http://www.electrictoolbox.com/article/mysql/cross-table-update/