[ordique]

hi all..
i need help to solve my problem..i have a few table to update in the same time..
but dont know how to write the correct sql statement..

[<?Wille?>]

http://forums.mysql.com/read.php?20,85813,85813

first reply to that post gives a example

UPDATE a 
INNER JOIN b USING (id) 
SET a.firstname='Pekka', a.lastname='Kuronen', 
    b.companyname='Suomi Oy',companyaddress='Mannerheimtie 123, Helsinki Suomi' 
WHERE a.id=1; 

[Nico]

Thread moved to Database section...

[ordique]

thanks <?Wille?>..
i will try it out..

[ordique]

PHP Code:

$result=mysql_query("UPDATE

    project1 p1, panel_detail pd, pic p, discreapancy d

    set p1.project_id='$ncr[0]', p1.Project_Name='$ncr[1]', p1.Part_No='$ncr3', 

    p1.Description='$ncr4', p1.Project_Date='$date', 

    p.Staff_id='$staffid', p.stamp_no='$stampno', p.Originator='$or', p.Location_pic='$location',

    pd.Discreapancy_id='$ncr', pd.Oper_No='$operno', pd.Drawing_No='$drawingno', pd.Rev='$rev',

    pd.Date_Cure='$date1', pd.GRR_Batch='$grrno', pd.S_No='$sn', pd.Jig='$jigno',

    pd.Batch_Quantity='$batchqtty', pd.JO_No='$jono',

    d.Quantity_Discrepant='$qttydiscrpnt', d.Discreapancy_detail='$discreapancy',

    d.Others='$notes', d.Defect_Code='$DCode'

    WHERE NCR_No='$ncrno'"); 

i already try it..but it doesnt update inside my database..
sori i'm very weak in programing..

[Nico]

PHP Code:

$query = "UPDATE
    project1 p1, panel_detail pd, pic p, discreapancy d
    set p1.project_id='$ncr[0]', p1.Project_Name='$ncr[1]', p1.Part_No='$ncr3', 
    p1.Description='$ncr4', p1.Project_Date='$date', 
    p.Staff_id='$staffid', p.stamp_no='$stampno', p.Originator='$or', p.Location_pic='$location',
    pd.Discreapancy_id='$ncr', pd.Oper_No='$operno', pd.Drawing_No='$drawingno', pd.Rev='$rev',
    pd.Date_Cure='$date1', pd.GRR_Batch='$grrno', pd.S_No='$sn', pd.Jig='$jigno',
    pd.Batch_Quantity='$batchqtty', pd.JO_No='$jono',
    d.Quantity_Discrepant='$qttydiscrpnt', d.Discreapancy_detail='$discreapancy',
    d.Others='$notes', d.Defect_Code='$DCode'
    WHERE NCR_No='$ncrno'";

if (!$result = mysql_query($query))
{
    echo '<pre>';
    printf("MySQL error: %s\n", mysql_error());
    printf("Query: %s", $query);
    exit('</pre>');
} 

Do it this way, and let MySQL tell you what the problem is. Also make sure the query string contains the expected values.

[<?Wille?>]

It might also be usefull for you to have a look at http://dev.mysql.com/doc/refman/5.0/en/commit.html

[ordique]

i already try it but still have error on it.it tell that

MySQL error: Column 'NCR_No' in where clause is ambiguous

[dustin56]

Originally Posted by ordique

MySQL error: Column 'NCR_No' in where clause is ambiguous

I believe this indicates that the field 'NCR_No' exists in more than one of the specified tables. You need to specify which table must be used in matching the where clause, or if this field must match in all tables which contain it, you have to specify that.
Like this

Code:

WHERE p.NCR_No='$ncrno'

or this

Code:

WHERE p.NCR_No='$ncrno' AND pl.NCR_No='$ncrno'

[ordique]

You are right dustin56..i have NCR_No in every table that i have b'cause it is foreign key.thank you for the information..